|
1 package com.daffodilwoods.daffodildb.server.sql99.ddl.utility;
2
3 import java.util.*;
4
5 import com.daffodilwoods.daffodildb.server.datadictionarysystem.*;
6 import com.daffodilwoods.daffodildb.server.serversystem.*;
7 import com.daffodilwoods.daffodildb.server.sql99.common.*;
8 import com.daffodilwoods.daffodildb.server.sql99.ddl.descriptors.*;
9 import com.daffodilwoods.daffodildb.server.sql99.dql.iterator.*;
10 import com.daffodilwoods.daffodildb.utils.*;
11 import com.daffodilwoods.database.general.*;
12 import com.daffodilwoods.database.resource.*;
13
14 public class QueryMaker {
15 public static String  getCreateIndexQuery(QualifiedIdentifier tableName, List columnsList,
16 String indexName) throws DException {
17 StringBuffer query = new StringBuffer ();
18 query.append("Create Index ")
19 .append(QuotedUtility.getUserIdentifier(indexName)).append(" On ")
20 .append(QuotedUtility.getUserIdentifier(tableName.catalog))
21 .append(".").append(QuotedUtility.getUserIdentifier(tableName.schema))
22 .append(".").append(QuotedUtility.getUserIdentifier(tableName.name))
23 .append("(").append(getColumnsList(columnsList)).append(")");
24 return query.toString();
25 }
26
27 public static String getColumnsList(List columns) throws DException {
28 int len = columns.size();
29 StringBuffer columnsList = new StringBuffer ();
30 for (int i = 0; i < len - 1; i++)
31 columnsList.append(QuotedUtility.getUserIdentifier( (String ) columns.get(
32 i))).append(",");
33 columnsList.append("").append(QuotedUtility.getUserIdentifier( (String )
34 columns.get(len - 1)));
35 return columnsList.toString();
36 }
37
38
39 public static String getUniqueORPrimaryConstraintQuery() {
40 StringBuffer query = new StringBuffer ();
41 query.append("select two.constraint_name,column_name from ").
42 append(SystemTables.key_column_usage_TableName).append(" as one, ").
43 append(SystemTables.table_constraints_TableName).append(" as two ").
44 append("where one.constraint_catalog = two.constraint_catalog ").
45 append(" and one.constraint_schema = two.constraint_schema ").
46 append(" and one.constraint_name = two.constraint_name").
47 append(" and two.table_catalog = ? ").
48 append(" and two.table_schema = ? ").
49 append(" and two.table_name = ? ").
50 append(" and two.constraint_type in( '").append(SqlKeywords.PRIMARY).
51 append(" ").append(SqlKeywords.KEY).append("'").
52 append(" ,'").append(SqlKeywords.UNIQUE).append("' )").
53 append(" order by two.constraint_name,one.ordinal_position ");
54 return query.toString();
55 }
56
57 public static String getTableDefinitionGrantQuery(QualifiedIdentifier tableName, String schemaOwner) {
58 StringBuffer query = new StringBuffer ();
59 query.append("grant ").append(SqlKeywords.SELECT)
60 .append(",").append(SqlKeywords.INSERT)
61 .append(",").append(SqlKeywords.UPDATE)
62 .append(",").append(SqlKeywords.DELETE)
63 .append(",").append(SqlKeywords.TRIGGER)
64 .append(",").append(SqlKeywords.REFERENCES)
65 .append(" On ")
66 .append(QuotedUtility.getUserIdentifier(tableName.catalog))
67 .append(".")
68 .append(QuotedUtility.getUserIdentifier(tableName.schema))
69 .append(".")
70 .append(QuotedUtility.getUserIdentifier(tableName.name))
71 .append(" To ")
72 .append(QuotedUtility.getUserIdentifier(schemaOwner))
73 .append(" with grant option granted by ").append(SqlKeywords.
74 CURRENT_USER);
75 return query.toString();
76 }
77
78 public static String getDomainDefinitionGrantQuery(QualifiedIdentifier domainName,
79 String user, boolean isGrantable) {
80 StringBuffer query = new StringBuffer ();
81 query.append("grant ").append(SqlKeywords.USAGE).append(" On DOMAIN ")
82 .append(QuotedUtility.getUserIdentifier(domainName.catalog))
83 .append(".")
84 .append(QuotedUtility.getUserIdentifier(domainName.schema))
85 .append(".")
86 .append(QuotedUtility.getUserIdentifier(domainName.name))
87 .append(" To ").append(QuotedUtility.getUserIdentifier(user))
88 .append(isGrantable ? " with grant option " : " ")
89 .append(" granted by ").append(SqlKeywords.CURRENT_USER);
90 return query.toString();
91 }
92
93 public static String getRoutineDefinitionGrantQuery(QualifiedIdentifier routineName,
94 String grantee, boolean isGrantable) throws DException {
95 StringBuffer grantQuery = new StringBuffer ();
96 if (isGrantable)
97 grantQuery.append("grant Execute on specific procedure ")
98 .append(QuotedUtility.getUserIdentifier(routineName.catalog))
99 .append(".")
100 .append(QuotedUtility.getUserIdentifier(routineName.schema))
101 .append(".")
102 .append(QuotedUtility.getUserIdentifier(routineName.name))
103 .append(" to \"").append(grantee)
104 .append("\" with grant option granted by CURRENT_USER");
105 else
106 grantQuery.append("grant Execute on specific procedure ")
107 .append(QuotedUtility.getUserIdentifier(routineName.catalog))
108 .append(".")
109 .append(QuotedUtility.getUserIdentifier(routineName.schema))
110 .append(".")
111 .append(QuotedUtility.getUserIdentifier(routineName.name))
112 .append(" to \"").append(grantee).append("\" granted by CURRENT_USER");
113 return grantQuery.toString();
114 }
115
116 public static String getRoutineDefinitionGrantQueryForFunction(QualifiedIdentifier routineName,
117 String grantee, boolean isGrantable) throws DException {
118 StringBuffer grantQuery = new StringBuffer ();
119 if (isGrantable)
120 grantQuery.append("grant Execute on specific function ")
121 .append(QuotedUtility.getUserIdentifier(routineName.catalog))
122 .append(".")
123 .append(QuotedUtility.getUserIdentifier(routineName.schema))
124 .append(".")
125 .append(QuotedUtility.getUserIdentifier(routineName.name))
126 .append(" to \"").append(grantee)
127 .append("\" with grant option granted by CURRENT_USER");
128 else
129 grantQuery.append("grant Execute on specific function ")
130 .append(QuotedUtility.getUserIdentifier(routineName.catalog))
131 .append(".")
132 .append(QuotedUtility.getUserIdentifier(routineName.schema))
133 .append(".")
134 .append(QuotedUtility.getUserIdentifier(routineName.name))
135 .append(" to \"").append(grantee).append("\" granted by CURRENT_USER");
136 return grantQuery.toString();
137 }
138
139 public static ArrayList getSpecificCorrespondingToProcedureName(_ServerSession
140 serverSession, String catalogName, String schemaName, String routineName) throws
141 DException {
142 ArrayList returnList = new ArrayList();
143 StringBuffer query = new StringBuffer ();
144 query.append("select specific_catalog, specific_schema, specific_name ")
145 .append(" from " + SystemTables.routines_TableName)
146 .append(" where routine_catalog = ? and routine_schema = ? ")
147 .append(" and routine_name = ?");
148 _SelectQueryIterator iter = SqlSchemaConstants.getIterator(serverSession, query.toString(),
149 new Object [] {catalogName, schemaName, routineName});
150 if (iter.first()) {
151 do {
152 Object [] values = (Object []) iter.getObject();
153 Object [] obj = new Object [] {values[0], values[1], values[2]};
154 returnList.add(obj);
155 } while (iter.next());
156 }
157 return returnList.size() == 0 ? null : returnList;
158 }
159
160 public static String getParameterCountQuery() {
161 StringBuffer query = new StringBuffer ();
162 query.append("select count(*) from ")
163 .append(SystemTables.parameters_TableName)
164 .append(" where specific_catalog = ? ")
165 .append("and specific_schema = ? and specific_name = ?");
166 return query.toString();
167 }
168
169 public static String getDropTableRevokeQuery(QualifiedIdentifier tableName,
170 String schemaOwner) {
171 StringBuffer query = new StringBuffer ();
172 query.append("REVOKE ALL PRIVILEGES ON ")
173 .append(QuotedUtility.getUserIdentifier(tableName.catalog)).append(".")
174 .append(QuotedUtility.getUserIdentifier(tableName.schema)).append(".")
175 .append(QuotedUtility.getUserIdentifier(tableName.name))
176 .append(" FROM ").append(QuotedUtility.getUserIdentifier(schemaOwner))
177 .append(" CASCADE");
178 return query.toString();
179 }
180
181 public static String makeDropTableQuery(QualifiedIdentifier tableName,
182 String dropBehaviour) {
183 StringBuffer query = new StringBuffer ();
184 query.append("Drop table ")
185 .append(QuotedUtility.getUserIdentifier(tableName.catalog)).
186 append(".")
187 .append(QuotedUtility.getUserIdentifier(tableName.schema)).
188 append(".")
189 .append(QuotedUtility.getUserIdentifier(tableName.name))
190 .append(" ")
191 .append(dropBehaviour);
192 return query.toString();
193 }
194
195 public static String getDropRoutineRevokeQuery(QualifiedIdentifier specificName,
196 String grantee) {
197 StringBuffer grantQuery = new StringBuffer ();
198 grantQuery.append("revoke Execute on specific procedure ")
199 .append(QuotedUtility.getUserIdentifier(specificName.catalog))
200 .append(".").append(QuotedUtility.getUserIdentifier(specificName.schema))
201 .append(".").append(QuotedUtility.getUserIdentifier(specificName.name))
202 .append(" from ").append(QuotedUtility.getUserIdentifier(grantee)).
203 append(" granted by CURRENT_USER cascade");
204 return grantQuery.toString();
205 }
206
207 public static String getDropRoutineRevokeQueryForFunction(QualifiedIdentifier specificName,
208 String grantee) {
209 StringBuffer grantQuery = new StringBuffer ();
210 grantQuery.append("revoke Execute on specific function ")
211 .append(QuotedUtility.getUserIdentifier(specificName.catalog))
212 .append(".").append(QuotedUtility.getUserIdentifier(specificName.schema))
213 .append(".").append(QuotedUtility.getUserIdentifier(specificName.name))
214 .append(" from ").append(QuotedUtility.getUserIdentifier(grantee)).
215 append(" granted by CURRENT_USER cascade");
216 return grantQuery.toString();
217 }
218
219 public static String getDropDomainRevokeQuery(QualifiedIdentifier domainName,
220 String schemaOwner) {
221 StringBuffer query = new StringBuffer ();
222 query.append("REVOKE USAGE ON DOMAIN ")
223 .append(QuotedUtility.getUserIdentifier(domainName.catalog)).
224 append(".")
225 .append(QuotedUtility.getUserIdentifier(domainName.schema)).
226 append(".")
227 .append(QuotedUtility.getUserIdentifier(domainName.name))
228 .append(" FROM ")
229 .append(QuotedUtility.getUserIdentifier(schemaOwner)).
230 append(" CASCADE");
231 return query.toString();
232 }
233
234 public static String getAlterTableAddCheckConstraintQuery(QualifiedIdentifier
235 tableName, String newCheckClause) {
236 StringBuffer query = new StringBuffer ();
237 query.append("alter table ")
238 .append(QuotedUtility.getUserIdentifier(tableName.catalog)).append(".")
239 .append(QuotedUtility.getUserIdentifier(tableName.schema)).append(".")
240 .append(QuotedUtility.getUserIdentifier(tableName.name))
241 .append(" add ")
242 .append(" CHECK (").append(newCheckClause).append(")");
243 return query.toString();
244 }
245
246 public static String getTableAndViewsQueryForSchema() {
247 StringBuffer query = new StringBuffer ();
248 query.append(" select * from ")
249 .append(SqlSchemaConstants.tables_TableName)
250 .append(" where table_catalog = ? and table_schema = ? ");
251 return query.toString();
252 }
253
254 public static String getDomainsQueryForSchema() {
255 StringBuffer clause = new StringBuffer ();
256 clause.append(" select * from ")
257 .append(SqlSchemaConstants.domains_TableName)
258 .append(" where domain_catalog = ? and domain_schema = ?");
259 return clause.toString();
260 }
261
262 public static String getRoutinesQueryForSchema() {
263 StringBuffer clause = new StringBuffer ();
264 clause.append(" select * from ")
265 .append(SqlSchemaConstants.routines_TableName)
266 .append(" where routine_catalog = ? and routine_schema = ? ");
267 return clause.toString();
268 }
269
270 public static String getSequenceQueryForSchema() {
271 StringBuffer clause = new StringBuffer ();
272 clause.append(" select * from ")
273 .append(SqlSchemaConstants.sequence_number_TableName)
274 .append(" where sequence_catalog = ? and sequence_schema = ? ");
275 return clause.toString();
276 }
277
278 public static String getTriggersQueryForSchema() {
279 StringBuffer clause = new StringBuffer ();
280 clause.append(" select * from ")
281 .append(SqlSchemaConstants.triggers_TableName)
282 .append(" where trigger_catalog = ? and trigger_schema = ? ");
283 return clause.toString();
284 }
285
286 public static String getSchemasQueryForUser() {
287 StringBuffer schemasQuery = new StringBuffer ();
288 schemasQuery.append("select * from ")
289 .append(SystemTables.schema_TableName)
290 .append(" where schema_owner = ?");
291 return schemasQuery.toString();
292 }
293
294 public static String getDropColumnRevokeQuery(QualifiedIdentifier tableName,
295 String columnName,
296 String schemaOwner) {
297 StringBuffer query = new StringBuffer ();
298 query.append("REVOKE INSERT(").append(QuotedUtility.getUserIdentifier(
299 columnName)).append("), UPDATE( ")
300 .append(QuotedUtility.getUserIdentifier(columnName))
301 .append(" ), SELECT( ")
302 .append(QuotedUtility.getUserIdentifier(columnName))
303 .append(" ), REFERENCES( ")
304 .append(QuotedUtility.getUserIdentifier(columnName))
305 .append(" ) ON TABLE ")
306 .append(QuotedUtility.getUserIdentifier(tableName.catalog)).
307 append(".")
308 .append(QuotedUtility.getUserIdentifier(tableName.schema)).
309 append(".")
310 .append(QuotedUtility.getUserIdentifier(tableName.name))
311 .append(" FROM ").append(QuotedUtility.getUserIdentifier(schemaOwner)).
312 append(" CASCADE ");
313 return query.toString();
314 }
315
316 public static String getDropColumnIndexQuery() {
317 StringBuffer query = new StringBuffer ();
318 query.append("select * from ").append(SystemTables.INDEXCOLUMNS)
319 .append(" where table_catalog = ? and table_schema = ? ")
320 .append("and table_name = ? and column_Name = ? ");
321 return query.toString();
322 }
323
324 public static String getDropFullTextColumnIndexQuery() {
325 StringBuffer query = new StringBuffer ();
326 query.append("select * from ").append(SystemTables.FULLTEXTCOLUMNINFO)
327 .append(" where table_catalog = ? and table_schema = ? ")
328 .append("and table_name = ? and column_Name = ? ");
329 return query.toString();
330 }
331
332 public static String getRoleAuthorizationsforGranteeOnly() {
333 StringBuffer roleAuthQuery = new StringBuffer ();
334 roleAuthQuery.append("select role_name from ")
335 .append(SystemTables.role_authorization_TableName).append(" where grantee = ?");
336 return roleAuthQuery.toString();
337 }
338
339 public static String getUsersQuery() {
340 StringBuffer userQuery = new StringBuffer ();
341 userQuery.append("select * from ")
342 .append(SystemTables.users_TableName).append(" where user_name = ?");
343 return userQuery.toString();
344 }
345
346 public static String getRolesQuery() {
347 StringBuffer rolesQuery = new StringBuffer ();
348 rolesQuery.append("select * from ")
349 .append(SystemTables.roles_TableName).append(" where role_name = ?");
350 return rolesQuery.toString();
351 }
352
353 public static String getIdentifiedRoleAuthorizationQuery() {
354 StringBuffer roleAuthQuery = new StringBuffer ();
355 roleAuthQuery.append("select * from ")
356 .append(SystemTables.role_authorization_TableName)
357 .append(" where role_name = ? and grantee = ? and grantor = ? ");
358 return roleAuthQuery.toString();
359 }
360
361 public static String getAllRoleAuthorizationsforGranteeAndPublic() {
362 StringBuffer roleAuthQuery = new StringBuffer ();
363 roleAuthQuery.append("select * from ")
364 .append(SystemTables.role_authorization_TableName).append(" where grantee IN (?,'PUBLIC')");
365 return roleAuthQuery.toString();
366 }
367
368 public static String getAllRoleAuthorizationsforGranteeOnly() {
369 StringBuffer roleAuthQuery = new StringBuffer ();
370 roleAuthQuery.append("select * from ")
371 .append(SystemTables.role_authorization_TableName).append(" where grantee = ? ");
372 return roleAuthQuery.toString();
373 }
374
375 public static String getAllTablePrivilegesForGranteesIn() {
376 StringBuffer tablePrivilegesQuery = new StringBuffer ();
377 tablePrivilegesQuery.append("select * from ")
378 .append(SystemTables.table_privileges_TableName).append(" where grantee in (?) ");
379 return tablePrivilegesQuery.toString();
380 }
381
382 public static String getAllColumnPrivilegesForGranteesIn() {
383 StringBuffer columnPrivilegesQuery = new StringBuffer ();
384 columnPrivilegesQuery.append("select * from ")
385 .append(SystemTables.column_privileges_TableName).append(" where grantee in (?) ");
386 return columnPrivilegesQuery.toString();
387 }
388
389 public static String getAllUsagePrivilegesForGranteesIn() {
390 StringBuffer usagePrivilegesQuery = new StringBuffer ();
391 usagePrivilegesQuery.append("select * from ")
392 .append(SystemTables.usage_privileges_TableName).append(" where grantee in (?) ");
393 return usagePrivilegesQuery.toString();
394 }
395
396 public static String getAllRoutinePrivilegesForGranteesIn() {
397 StringBuffer routinePrivilegesQuery = new StringBuffer ();
398 routinePrivilegesQuery.append("select * from ")
399 .append(SystemTables.routine_privileges_TableName).append(" where grantee in (?) ");
400 return routinePrivilegesQuery.toString();
401 }
402
403 public static String getIsValidRole() {
404 StringBuffer roleValidQuery = new StringBuffer ();
405 roleValidQuery.append("select * from ")
406 .append(SystemTables.roles_TableName).append(" where role_name = ? ");
407 return roleValidQuery.toString();
408 }
409 }
410 |
Popular Tags |
Java API By Example, From Geeks To Geeks.