|
1
24
25 package org.objectweb.cjdbc.scenario.tools.util;
26
27 import java.sql.Connection  ;
28 import java.sql.DriverManager ;
29 import java.sql.SQLException ;
30 import java.sql.Types ;
31 import java.util.ArrayList ;
32 import java.util.Random ;
33
34 import org.objectweb.cjdbc.common.log.Trace;
35 import org.objectweb.cjdbc.common.sql.schema.DatabaseColumn;
36 import org.objectweb.cjdbc.common.sql.schema.DatabaseSQLMetaData;
37 import org.objectweb.cjdbc.common.sql.schema.DatabaseSchema;
38 import org.objectweb.cjdbc.common.sql.schema.DatabaseTable;
39
40
46 public final class QueryGenerator
47 {
48 Integer maxUniqueId = new Integer (2000050);
49 Random rand;
50 double readWriteRatio;
51 double writeDeleteRatio;
52 double insertUpdateRatio;
53 double orderByRatio;
54 DatabaseSchema schema;
55 ArrayList tables;
56 int tableSize;
57 private double schemaUpdateRatio;
58 private double createDropRatio;
59
60
62
68 public static void main(String [] args) throws Exception
69 {
70 Class.forName("org.objectweb.cjdbc.driver.Driver");
71 Connection connection = DriverManager.getConnection(
72 "jdbc:cjdbc://localhost/myDB", "user", "");
73
74 QueryGenerator generator = new QueryGenerator(connection);
75 String sql;
76 for (int i = 0; i < 20000; i++)
77 {
78 sql = generator.generateWriteQuery();
79 System.out.println(sql);
80 synchronized (generator)
81 {
82 generator.wait(10);
83 }
84 connection.createStatement().execute(sql);
85 }
86 }
87
88
91 public QueryGenerator(DatabaseSchema schema)
92 {
93 rand = new Random ();
94 readWriteRatio = 0.5;
95 writeDeleteRatio = 0.8;
96 insertUpdateRatio = 0.5;
97 orderByRatio = 0.9;
98 schemaUpdateRatio = 0.1;
99 createDropRatio = 0.8;
100 this.schema = schema;
101 tables = schema.getTables();
102 tableSize = tables.size();
103 }
104
105
112 public QueryGenerator(Connection connection) throws SQLException
113 {
114 this(new DatabaseSQLMetaData(Trace.getLogger("niko"), connection, 4, false,null)
115 .createDatabaseSchema());
116 }
118
119
124 public final String generateReadQuery()
125 {
126 return generateSelectQuery();
127 }
128
129
134 public final String generateSelectQuery()
135 {
136 StringBuffer buffer = new StringBuffer ();
137 DatabaseTable table = getTable();
138
139 ArrayList columns = table.getColumns();
140 int columnSize = columns.size();
141
142 buffer.append("SELECT ");
143 int next = rand.nextInt(columnSize + 1);
144 if (next >= columnSize)
145 {
146 buffer.append(" * ");
147 }
148 else
149 {
150 for (int i = 0; i <= next; i++)
151 {
152 if (i != 0)
153 buffer.append(",");
154 String name = ((DatabaseColumn) columns.get(i)).getName();
155 buffer.append(name);
157 }
158 }
159 buffer.append(" FROM ");
160
161 buffer.append(table.getName());
162 if (rand.nextBoolean())
163 {
164 buffer.append(getRelation(table, true));
165 }
166 if (nextIsInRatio(orderByRatio))
167 buffer.append(generateOrderBy(table));
168 return buffer.toString();
169 }
170
171
177 private String generateOrderBy(DatabaseTable table)
178 {
179 StringBuffer buffer = new StringBuffer ();
180 buffer.append(" ORDER BY ");
181 ArrayList columns = table.getColumns();
182 int colSize = columns.size();
183 int nb = rand.nextInt(colSize);
184 for (int i = 0; i <= nb; i++)
185 {
186 if (i != 0)
187 buffer.append(",");
188 buffer.append(((DatabaseColumn) columns.get(i)).getName());
189 int aa = rand.nextInt(3);
190 switch (aa)
191 {
192 case 0 :
193 buffer.append(" ASC ");
194 break;
195 case 1 :
196 buffer.append(" DESC ");
197 break;
198 case 2 :
199 break;
200 default :
201 throw new RuntimeException ("Unexpected value in generateOrderBy");
202 }
203 }
204 return buffer.toString();
205 }
206
207 private String getRelation(DatabaseTable table, boolean useLowerAndGreater)
208 {
209 ArrayList unique = table.getUniqueColumns();
210 int uniqueSize = unique.size();
211 StringBuffer buffer = new StringBuffer ();
212 if (uniqueSize > 0)
213 {
214 buffer.append(" WHERE ");
215 int nextU = rand.nextInt(uniqueSize);
216 DatabaseColumn col = (DatabaseColumn) unique.get(nextU);
217 buffer.append(col.getName());
218 if (useLowerAndGreater)
219 {
220 int ee = rand.nextInt(3);
221 if (ee == 0)
222 buffer.append("<");
223 if (ee == 1)
224 buffer.append("=");
225 if (ee == 2)
226 buffer.append(">");
227 }
228 else
229 {
230 buffer.append("=");
231 }
232 buffer.append(rand.nextInt(getMaxUniqueId()));
233 }
234 return buffer.toString();
235 }
236
237 private int getMaxUniqueId()
238 {
239 synchronized (maxUniqueId)
240 {
241 return maxUniqueId.intValue();
242 }
243 }
244
245 private int getNextMaxUniqueId()
246 {
247 synchronized (maxUniqueId)
248 {
249 int max = (maxUniqueId.intValue());
250 max++;
251 maxUniqueId = new Integer (max);
252 return maxUniqueId.intValue();
253 }
254 }
255
256 private DatabaseTable getTable()
257 {
258 int next = rand.nextInt(tableSize);
259 return (DatabaseTable) tables.get(next);
260 }
261
262
268 public final String generateWriteQuery()
269 {
270 if (nextIsInRatio(schemaUpdateRatio))
271 return generateSchemaUpdateQuery();
272 if (nextIsInRatio(writeDeleteRatio))
273 if (nextIsInRatio(insertUpdateRatio))
274 return generateInsert();
275 else
276 return generateUpdate();
277 else
278 return generateDelete();
279 }
280
281 public final String generateSchemaUpdateQuery()
282 {
283 if (nextIsInRatio(createDropRatio))
284 return generateCreateQuery();
285 else
286 return generateDropQuery();
287 }
288
289 public final String generateCreateQuery()
290 {
291 StringBuffer buffer = new StringBuffer ();
292 buffer.append("CREATE TABLE temp");
293 buffer.append(getRandomString(20));
294 buffer.append("( ");
295 int columns = rand.nextInt(10)+1;
296 for (int i = 0; i < columns; i++)
297 {
298 if (i != 0)
299 buffer.append(",");
300 buffer.append(getRandomString(20) + " " + getRandomType());
301 }
302 buffer.append(")");
303 return buffer.toString();
304 }
305
306 public final String generateDropQuery()
307 {
308 return "DROP TABLE " + getRandomTable();
309 }
310
311 private boolean nextIsInRatio(double ratio)
312 {
313 double next = rand.nextDouble();
314 if (next < ratio)
315 return true;
316 else
317 return false;
318 }
319
320
325 public final String generateDelete()
326 {
327 StringBuffer buffer = new StringBuffer ();
328 buffer.append("DELETE FROM ");
329 DatabaseTable table = getTable();
330 buffer.append(table.getName());
331 buffer.append(getRelation(table, false));
332 return buffer.toString();
333 }
334
335
340 public final String generateUpdate()
341 {
342 StringBuffer buffer = new StringBuffer ();
343 DatabaseTable table = getTable();
344 buffer.append("UPDATE ");
345 buffer.append(table.getName());
346 buffer.append(" SET ");
347 DatabaseColumn column = getRandomNonUniqueColumn(table);
348 buffer.append(column.getName() + "=" + getRandomValue(column));
349 buffer.append(getRelation(table, true));
350 return buffer.toString();
351 }
352
353 private DatabaseColumn getRandomNonUniqueColumn(DatabaseTable table)
354 {
355 return getRandomColumn(table, false);
356 }
357
358 private DatabaseColumn getRandomColumn(DatabaseTable table,
359 boolean allowUnique)
360 {
361 ArrayList columns = table.getColumns();
362 int size = columns.size();
363 int next = rand.nextInt(size);
364 DatabaseColumn column = (DatabaseColumn) columns.get(next);
365 if (column.isUnique() && !allowUnique)
366 return getRandomNonUniqueColumn(table);
367 else
368 return column;
369 }
370
371
376 public final String generateInsert()
377 {
378 StringBuffer buffer = new StringBuffer ();
379 StringBuffer buffer2 = new StringBuffer ();
380 DatabaseTable table = getTable();
381 buffer.append("INSERT INTO ");
382 buffer.append(table.getName());
383 buffer.append(" (");
384 buffer2.append(" (");
385 ArrayList columns = table.getColumns();
386 int colSize = columns.size();
387 DatabaseColumn column;
388 for (int i = 0; i < colSize; i++)
389 {
390 if (i != 0)
391 {
392 buffer.append(",");
393 buffer2.append(",");
394 }
395 column = (DatabaseColumn) columns.get(i);
396 buffer.append(column.getName());
397 buffer2.append(getRandomValue(column));
398 }
399 buffer.append(") ");
400 buffer2.append(") ");
401 buffer.append("VALUES " + buffer2);
402 return buffer.toString();
403 }
404
405 private final String getRandomValue(DatabaseColumn column)
406 {
407 int type = column.getType();
408 switch (type)
409 {
410 case Types.VARCHAR :
411 return "'" + getRandomString(20) + "'";
412 case Types.INTEGER :
413 if (column.isUnique())
414 return String.valueOf(getNextMaxUniqueId());
415 else
416 return String.valueOf(rand.nextInt(Integer.MAX_VALUE));
417 case Types.DOUBLE :
418 return String.valueOf(rand.nextDouble());
419 case Types.DECIMAL :
420 return String.valueOf(rand.nextDouble()
421 * rand.nextInt(Integer.MAX_VALUE));
422 default :
423 return "0";
424 }
425 }
426
427 private final String getRandomString(int size)
428 {
429 StringBuffer buffer = new StringBuffer ();
430 int length = rand.nextInt(size)+1;
431 for (int i = 0; i < length; i++)
432 buffer.append(Character.forDigit(rand.nextInt(Character.MAX_RADIX),
433 Character.MAX_RADIX));
434 return buffer.toString();
435 }
436
437 private final String getRandomType()
438 {
439 int type = rand.nextInt(4);
440 switch (type)
441 {
442 case 0 :
443 return "INT";
444 case 1 :
445 return "VARCHAR";
446 case 2 :
447 return "DECIMAL";
448 case 3 :
449 return "BOOLEAN";
450 default :
451 return null;
452 }
453 }
454
455 private String getRandomTable()
456 {
457 ArrayList tables = schema.getTables();
458 int size = tables.size();
459 return ((DatabaseTable) tables.get(rand.nextInt(size))).getName();
460 }
461
462
467 public final double getReadWriteRatio()
468 {
469 return readWriteRatio;
470 }
471
472
477 public final void setReadWriteRatio(double readWriteRatio)
478 {
479 this.readWriteRatio = readWriteRatio;
480 }
481
482
487 public final String generateQuery()
488 {
489 if (nextIsInRatio(readWriteRatio))
490 return generateReadQuery();
491 else
492 return generateWriteQuery();
493 }
494
495
500 public final double getInsertUpdateRatio()
501 {
502 return insertUpdateRatio;
503 }
504
505
510 public final void setInsertUpdateRatio(double insertUpdateRatio)
511 {
512 this.insertUpdateRatio = insertUpdateRatio;
513 }
514
515
520 public final double getOrderByRatio()
521 {
522 return orderByRatio;
523 }
524
525
530 public final void setOrderByRatio(double orderByRatio)
531 {
532 this.orderByRatio = orderByRatio;
533 }
534
535
540 public final double getWriteDeleteRatio()
541 {
542 return writeDeleteRatio;
543 }
544
545
550 public final void setWriteDeleteRatio(double writeDeleteRatio)
551 {
552 this.writeDeleteRatio = writeDeleteRatio;
553 }
554
555
560 public double getCreateDropRatio()
561 {
562 return createDropRatio;
563 }
564
565
570 public void setCreateDropRatio(double createDropRatio)
571 {
572 this.createDropRatio = createDropRatio;
573 }
574
575
580 public double getSchemaUpdateRatio()
581 {
582 return schemaUpdateRatio;
583 }
584
585
590 public void setSchemaUpdateRatio(double schemaUpdateRatio)
591 {
592 this.schemaUpdateRatio = schemaUpdateRatio;
593 }
594 }
595 |
Popular Tags |
Java API By Example, From Geeks To Geeks.