|
1
65
66
67 package org.hsqldb.util;
68
69 import java.io.BufferedReader  ;
70 import java.io.FileReader ;
71 import java.io.FileWriter ;
72 import java.io.IOException ;
73 import java.sql.SQLException ;
74 import java.sql.Statement ;
75 import java.util.Random ;
76
77
84
93 class DatabaseManagerCommon {
94
95 private static Random rRandom = new Random (100);
96 static String [] selectHelp = {
97 "SELECT * FROM ",
98 "SELECT [LIMIT n m] [DISTINCT] \n"
99 + "{ selectExpression | table.* | * } [, ... ] \n"
100 + "[INTO [CACHED|TEMP|TEXT] newTable] \n" + "FROM tableList \n"
101 + "[WHERE Expression] \n"
102 + "[ORDER BY selectExpression [{ASC | DESC}] [, ...] ] \n"
103 + "[GROUP BY Expression [, ...] ] \n"
104 + "[UNION [ALL] selectStatement]"
105 };
106 static String [] insertHelp = {
107 "INSERT INTO ",
108 "INSERT INTO table [ (column [,...] ) ] \n"
109 + "{ VALUES(Expression [,...]) | SelectStatement }"
110 };
111 static String [] updateHelp = {
112 "UPDATE ",
113 "UPDATE table SET column = Expression [, ...] \n"
114 + "[WHERE Expression]"
115 };
116 static String [] deleteHelp = {
117 "DELETE FROM ", "DELETE FROM table [WHERE Expression]"
118 };
119 static String [] createTableHelp = {
120 "CREATE TABLE ",
121 "CREATE [TEMP] [CACHED|MEMORY|TEXT] TABLE name \n"
122 + "( columnDefinition [, ...] ) \n\n" + "columnDefinition: \n"
123 + "column DataType [ [NOT] NULL] [PRIMARY KEY] \n" + "DataType: \n"
124 + "{ INTEGER | DOUBLE | VARCHAR | DATE | TIME |... }"
125 };
126 static String [] dropTableHelp = {
127 "DROP TABLE ", "DROP TABLE table"
128 };
129 static String [] createIndexHelp = {
130 "CREATE INDEX ",
131 "CREATE [UNIQUE] INDEX index ON \n" + "table (column [, ...])"
132 };
133 static String [] dropIndexHelp = {
134 "DROP INDEX ", "DROP INDEX table.index"
135 };
136 static String [] checkpointHelp = {
137 "CHECKPOINT", "(HSQLDB SQL only)"
138 };
139 static String [] scriptHelp = {
140 "SCRIPT", "SCRIPT ['file']\n\n" + "(HSQLDB SQL only)"
141 };
142 static String [] shutdownHelp = {
143 "SHUTDOWN", "SHUTDOWN [COMPACT|IMMEDIATELY]\n\n" + "(HSQLDB SQL only)"
144 };
145 static String [] setHelp = {
146 "SET ",
147 "AUTOCOMMIT { TRUE | FALSE }\n" + "IGNORECASE { TRUE | FALSE }\n"
148 + "LOGSIZE size\n" + "MAXROWS maxrows\n" + "PASSWORD password\n"
149 + "READONLY { TRUE | FALSE }\n"
150 + "REFERENTIAL_INTEGRITY { TRUE | FALSE }\n"
151 + "TABLE table READONLY { TRUE | FALSE }\n"
152 + "TABLE table SOURCE \"file\" [DESC]\n"
153 + "WRITE_DELAY { TRUE | FALSE }\n\n" + "(HSQLDB SQL only)"
154 };
155 static String [] testHelp = {
156 "-->>>TEST<<<-- ;\n" + "--#1000;\n" + "DROP TABLE Test IF EXISTS;\n"
157 + "CREATE TABLE Test(\n" + " Id INTEGER PRIMARY KEY,\n"
158 + " FirstName VARCHAR(20),\n" + " Name VARCHAR(50),\n"
159 + " ZIP INTEGER) ;\n" + "INSERT INTO Test \n"
160 + " VALUES(#,'Julia','Peterson-Clancy',#) ;\n"
161 + "UPDATE Test SET Name='Hans' WHERE Id=# ;\n"
162 + "SELECT * FROM Test WHERE Id=# ;\n"
163 + "DELETE FROM Test WHERE Id=# ;\n" + "DROP TABLE Test IF EXISTS;",
164 "This test script is parsed by the DatabaseManager\n"
165 + "It may be changed manually. Rules:\n"
166 + "- it must start with -->>>TEST<<<--.\n"
167 + "- each line must end with ';' (no spaces after)\n"
168 + "- lines starting with -- are comments\n"
169 + "- lines starting with --#<count> means set new count\n"
170 };
171 static String [] testDataSql = {
172 "SELECT * FROM Product", "SELECT * FROM Invoice",
173 "SELECT * FROM Item",
174 "SELECT * FROM Customer a INNER JOIN Invoice i ON a.ID=i.CustomerID",
175 "SELECT * FROM Customer a LEFT OUTER JOIN Invoice i ON a.ID=i.CustomerID",
176 "SELECT * FROM Invoice d INNER JOIN Item i ON d.ID=i.InvoiceID",
177 "SELECT * FROM Customer WHERE Street LIKE '1%' ORDER BY Lastname",
178 "SELECT a.id, a.firstname, a.lastname, count(i.Total) \"COUNT\", "
179 + "COALESCE(sum(i.Total), 0) \"TOTAL\", COALESCE(AVG(i.Total),0) \"AVG\" FROM Customer a "
180 + "LEFT OUTER JOIN Invoice i ON a.ID=i.CustomerID GROUP BY a.id, a.firstname, a.lastname"
181 };
182
183
191 static String random(String [] s) {
192 return s[random(s.length)];
193 }
194
195
203 static int random(int i) {
204
205 i = rRandom.nextInt() % i;
206
207 return i < 0 ? -i
208 : i;
209 }
210
211
215 static void createTestTables(Statement sStatement) {
216
217 String [] demo = {
218 "DROP TABLE Item IF EXISTS;", "DROP TABLE Invoice IF EXISTS;",
219 "DROP TABLE Product IF EXISTS;", "DROP TABLE Customer IF EXISTS;",
220 "CREATE TABLE Customer(ID INTEGER PRIMARY KEY,FirstName VARCHAR,"
221 + "LastName VARCHAR,Street VARCHAR,City VARCHAR);",
222 "CREATE TABLE Product(ID INTEGER PRIMARY KEY,Name VARCHAR,"
223 + "Price DECIMAL);",
224 "CREATE TABLE Invoice(ID INTEGER PRIMARY KEY,CustomerID INTEGER,"
225 + "Total DECIMAL, FOREIGN KEY (CustomerId) "
226 + "REFERENCES Customer(ID) ON DELETE CASCADE);",
227 "CREATE TABLE Item(InvoiceID INTEGER,Item INTEGER,"
228 + "ProductID INTEGER,Quantity INTEGER,Cost DECIMAL,"
229 + "PRIMARY KEY(InvoiceID,Item), "
230 + "FOREIGN KEY (InvoiceId) REFERENCES "
231 + "Invoice (ID) ON DELETE CASCADE, FOREIGN KEY (ProductId) "
232 + "REFERENCES Product(ID) ON DELETE CASCADE);"
233 };
234
235 for (int i = 0; i < demo.length; i++) {
236
237 try {
239 sStatement.execute(demo[i]);
240 } catch (SQLException e) {
241 ;
242 }
243 }
244 }
245
246
250 static String createTestData(Statement sStatement) throws SQLException {
251
252 String [] name = {
253 "White", "Karsen", "Smith", "Ringer", "May", "King", "Fuller",
254 "Miller", "Ott", "Sommer", "Schneider", "Steel", "Peterson",
255 "Heiniger", "Clancy"
256 };
257 String [] firstname = {
258 "Mary", "James", "Anne", "George", "Sylvia", "Robert", "Janet",
259 "Michael", "Andrew", "Bill", "Susanne", "Laura", "Bob", "Julia",
260 "John"
261 };
262 String [] street = {
263 "Upland Pl.", "College Av.", "- 20th Ave.", "Seventh Av."
264 };
265 String [] city = {
266 "New York", "Dallas", "Boston", "Chicago", "Seattle",
267 "San Francisco", "Berne", "Oslo", "Paris", "Lyon", "Palo Alto",
268 "Olten"
269 };
270 String [] product = {
271 "Iron", "Ice Tea", "Clock", "Chair", "Telephone", "Shoe"
272 };
273 int max = 50;
274
275 sStatement.execute("SET REFERENTIAL_INTEGRITY FALSE");
276
277 for (int i = 0; i < max; i++) {
278 sStatement.execute("INSERT INTO Customer VALUES(" + i + ",'"
279 + random(firstname) + "','" + random(name)
280 + "','" + random(554) + " " + random(street)
281 + "','" + random(city) + "')");
282 sStatement.execute("INSERT INTO Product VALUES(" + i + ",'"
283 + random(product) + " " + random(product)
284 + "'," + (20 + 2 * random(120)) + ")");
285 sStatement.execute("INSERT INTO Invoice VALUES(" + i + ","
286 + random(max) + ",0.0)");
287
288 for (int j = random(20) + 2; j >= 0; j--) {
289 sStatement.execute("INSERT INTO Item VALUES(" + i + "," + j
290 + "," + random(max) + ","
291 + (1 + random(24)) + ",1.5)");
292 }
293 }
294
295 sStatement.execute("SET REFERENTIAL_INTEGRITY TRUE");
296 sStatement.execute("UPDATE Product SET Price=ROUND(Price*.1,2)");
297 sStatement.execute(
298 "UPDATE Item SET Cost=Cost*"
299 + "SELECT Price FROM Product prod WHERE ProductID=prod.ID");
300 sStatement.execute(
301 "UPDATE Invoice SET Total=SELECT SUM(Cost*"
302 + "Quantity) FROM Item WHERE InvoiceID=Invoice.ID");
303
304 return ("SELECT * FROM Customer");
305 }
306
307
314 static String readFile(String file) {
315
316 try {
317 FileReader reader = new FileReader (file);
318 BufferedReader read = new BufferedReader (reader);
319 StringBuffer b = new StringBuffer ();
320 String s = null;
321 int count = 0;
322
323 while ((s = read.readLine()) != null) {
324 count++;
325
326 b.append(s);
327 b.append('\n');
328 }
329
330 read.close();
331 reader.close();
332
333 return b.toString();
334 } catch (IOException e) {
335 return e.getMessage();
336 }
337 }
338
339
346 static void writeFile(String file, String text) {
347
348 try {
349 FileWriter write = new FileWriter (file);
350
351 write.write(text.toCharArray());
352 write.close();
353 } catch (IOException e) {
354 e.printStackTrace();
355 }
356 }
357
358
369 static long testStatement(Statement sStatement, String sql,
370 int max) throws SQLException {
371
372 long start = System.currentTimeMillis();
373
374 if (sql.indexOf('#') == -1) {
375 max = 1;
376 }
377
378 for (int i = 0; i < max; i++) {
379 String s = sql;
380
381 while (true) {
382 int j = s.indexOf("#r#");
383
384 if (j == -1) {
385 break;
386 }
387
388 s = s.substring(0, j) + ((int) (Math.random() * i))
389 + s.substring(j + 3);
390 }
391
392 while (true) {
393 int j = s.indexOf('#');
394
395 if (j == -1) {
396 break;
397 }
398
399 s = s.substring(0, j) + i + s.substring(j + 1);
400 }
401
402 sStatement.execute(s);
403 }
404
405 return (System.currentTimeMillis() - start);
406 }
407
408 private DatabaseManagerCommon() {}
409 }
410 |
Popular Tags |
Java API By Example, From Geeks To Geeks.