001: /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the Hypersonic SQL Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: *
030: * This software consists of voluntary contributions made by many individuals
031: * on behalf of the Hypersonic SQL Group.
032: *
033: *
034: * For work added by the HSQL Development Group:
035: *
036: * Copyright (c) 2001-2005, The HSQL Development Group
037: * All rights reserved.
038: *
039: * Redistribution and use in source and binary forms, with or without
040: * modification, are permitted provided that the following conditions are met:
041: *
042: * Redistributions of source code must retain the above copyright notice, this
043: * list of conditions and the following disclaimer.
044: *
045: * Redistributions in binary form must reproduce the above copyright notice,
046: * this list of conditions and the following disclaimer in the documentation
047: * and/or other materials provided with the distribution.
048: *
049: * Neither the name of the HSQL Development Group nor the names of its
050: * contributors may be used to endorse or promote products derived from this
051: * software without specific prior written permission.
052: *
053: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
054: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
055: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
056: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
057: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
058: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
059: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
060: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
061: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
062: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
063: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
064: */
065:
066: package org.hsqldb.util;
067:
068: import java.io.BufferedReader;
069: import java.io.FileReader;
070: import java.io.FileWriter;
071: import java.io.IOException;
072: import java.sql.SQLException;
073: import java.sql.Statement;
074: import java.util.Random;
075:
076: // sqlbob@users 20020401 - patch 1.7.0 by sqlbob (RMP) - enhancements
077: // sqlbob@users 20020407 - patch 1.7.0 - reengineering
078: // nickferguson@users 20021005 - patch 1.7.1 - enhancements
079: // fredt@users 20021012 - patch 1.7.1 - changes to test database DDL
080: // weconsultants@users 20041116 - patch 1.8.0 - in 'TestHelp' added 'IF EXISTS for both DROPS.
081: // Now catching the execption that was never caught before.
082:
083: /**
084: * Common code in Swing and AWT versions of DatabaseManager
085: *
086: * New class based on Hypersonic original
087: *
088: * @author Thomas Mueller (Hypersonic SQL Group)
089: * @version 1.8.0
090: * @since 1.7.0
091: */
092: class DatabaseManagerCommon {
093:
094: private static Random rRandom = new Random(100);
095: static String[] selectHelp = {
096: "SELECT * FROM ",
097:
098: "SELECT [LIMIT n m] [DISTINCT] \n"
099: + "{ selectExpression | table.* | * } [, ... ] \n"
100: + "[INTO [CACHED|TEMP|TEXT] newTable] \n"
101: + "FROM tableList \n"
102: + "[WHERE Expression] \n"
103: + "[ORDER BY selectExpression [{ASC | DESC}] [, ...] ] \n"
104: + "[GROUP BY Expression [, ...] ] \n" //
105: + "[UNION [ALL] selectStatement]" };
106: static String[] insertHelp = {
107: "INSERT INTO ",
108: "INSERT INTO table [ (column [,...] ) ] \n"
109: + "{ VALUES(Expression [,...]) | SelectStatement }" };
110: static String[] updateHelp = {
111: "UPDATE ",
112: "UPDATE table SET column = Expression [, ...] \n"
113: + "[WHERE Expression]" };
114: static String[] deleteHelp = { "DELETE FROM ",
115: "DELETE FROM table [WHERE Expression]" };
116: static String[] createTableHelp = {
117: "CREATE TABLE ",
118: "CREATE [TEMP] [CACHED|MEMORY|TEXT] TABLE name \n"
119: + "( columnDefinition [, ...] ) \n\n"
120: + "columnDefinition: \n"
121: + "column DataType [ [NOT] NULL] [PRIMARY KEY] \n"
122: + "DataType: \n"
123: + "{ INTEGER | DOUBLE | VARCHAR | DATE | TIME |... }" };
124: static String[] dropTableHelp = { "DROP TABLE ", "DROP TABLE table" };
125: static String[] createIndexHelp = {
126: "CREATE INDEX ",
127: "CREATE [UNIQUE] INDEX index ON \n"
128: + "table (column [, ...])" };
129: static String[] dropIndexHelp = { "DROP INDEX ",
130: "DROP INDEX table.index" };
131: static String[] checkpointHelp = { "CHECKPOINT",
132: "(HSQLDB SQL only)" };
133: static String[] scriptHelp = { "SCRIPT",
134: "SCRIPT ['file']\n\n" + "(HSQLDB SQL only)" };
135: static String[] shutdownHelp = {
136: "SHUTDOWN",
137: "SHUTDOWN [COMPACT|IMMEDIATELY|SCRIPT]\n\n"
138: + "(HSQLDB SQL only)" };
139: static String[] setHelp = {
140: "SET ",
141:
142: "SET AUTOCOMMIT { TRUE | FALSE }\n"
143: + "SET DATABASE COLLATION \"<collationname>\"\n"
144: + "SET CHECKPOINT DEFRAG <size>\n"
145: + "SET IGNORECASE { TRUE | FALSE }\n"
146: + "SET INITIAL SCHEMA <schemaname>\n" //
147: + "SET LOGSIZE <size>\n" //
148: + "SET MAXROWS maxrows\n"
149: + "SET PASSWORD <password>\n" //
150: + "SET PROPERTY \"<propname>\" <propvalue>\n"
151: + "SET READONLY { TRUE | FALSE }\n"
152: + "SET REFERENTIAL_INTEGRITY { TRUE | FALSE }\n"
153: + "SET SCHEMA <schemaname>\n"
154: + "SET SCRIPTFORMAT { TEXT | BINARY | COMPRESSED }\n"
155: + "SET TABLE INDEX <tablename> '<index1rootPos>...'\n"
156: + "SET TABLE <tablename> READONLY { TRUE | FALSE }\n"
157: + "SET TABLE <tablename> SOURCE \"<file>\" [DESC]\n"
158: + "SET WRITE_DELAY { TRUE | FALSE | <seconds> | <ms> MILLIS }"
159: + "\n\n" + "(HSQLDB SQL only)" };
160: static String[] testHelp = {
161: "-->>>TEST<<<-- ;\n" + "--#1000;\n"
162: + "DROP TABLE Test IF EXISTS;\n"
163: + "CREATE TABLE Test(\n"
164: + " Id INTEGER PRIMARY KEY,\n"
165: + " FirstName VARCHAR(20),\n"
166: + " Name VARCHAR(50),\n" + " ZIP INTEGER) ;\n"
167: + "INSERT INTO Test \n"
168: + " VALUES(#,'Julia','Peterson-Clancy',#) ;\n"
169: + "UPDATE Test SET Name='Hans' WHERE Id=# ;\n"
170: + "SELECT * FROM Test WHERE Id=# ;\n"
171: + "DELETE FROM Test WHERE Id=# ;\n"
172: + "DROP TABLE Test IF EXISTS;",
173: "This test script is parsed by the DatabaseManager\n"
174: + "It may be changed manually. Rules:\n"
175: + "- it must start with -->>>TEST<<<--.\n"
176: + "- each line must end with ';' (no spaces after)\n"
177: + "- lines starting with -- are comments\n"
178: + "- lines starting with --#<count> means set new count\n" };
179: static String[] testDataSql = {
180: "SELECT * FROM Product", //
181: "SELECT * FROM Invoice", //
182: "SELECT * FROM Item",
183: "SELECT * FROM Customer a INNER JOIN Invoice i ON a.ID=i.CustomerID",
184: "SELECT * FROM Customer a LEFT OUTER JOIN Invoice i ON a.ID=i.CustomerID",
185: "SELECT * FROM Invoice d INNER JOIN Item i ON d.ID=i.InvoiceID",
186: "SELECT * FROM Customer WHERE Street LIKE '1%' ORDER BY Lastname",
187: "SELECT a.id, a.firstname, a.lastname, count(i.Total) \"COUNT\", "
188: + "COALESCE(sum(i.Total), 0) \"TOTAL\", COALESCE(AVG(i.Total),0) \"AVG\" FROM Customer a "
189: + "LEFT OUTER JOIN Invoice i ON a.ID=i.CustomerID GROUP BY a.id, a.firstname, a.lastname" };
190:
191: /**
192: * Method declaration
193: *
194: *
195: * @param s
196: *
197: * @return
198: */
199: static String random(String[] s) {
200: return s[random(s.length)];
201: }
202:
203: /**
204: * Method declaration
205: *
206: *
207: * @param i
208: *
209: * @return
210: */
211: static int random(int i) {
212:
213: i = rRandom.nextInt() % i;
214:
215: return i < 0 ? -i : i;
216: }
217:
218: /**
219: * Method declaration
220: *
221: */
222: static void createTestTables(Statement sStatement) {
223:
224: String[] demo = {
225: "DROP TABLE Item IF EXISTS;",
226: "DROP TABLE Invoice IF EXISTS;",
227: "DROP TABLE Product IF EXISTS;",
228: "DROP TABLE Customer IF EXISTS;",
229: "CREATE TABLE Customer(ID INTEGER PRIMARY KEY,FirstName VARCHAR,"
230: + "LastName VARCHAR,Street VARCHAR,City VARCHAR);",
231: "CREATE TABLE Product(ID INTEGER PRIMARY KEY,Name VARCHAR,"
232: + "Price DECIMAL);",
233: "CREATE TABLE Invoice(ID INTEGER PRIMARY KEY,CustomerID INTEGER,"
234: + "Total DECIMAL, FOREIGN KEY (CustomerId) "
235: + "REFERENCES Customer(ID) ON DELETE CASCADE);",
236: "CREATE TABLE Item(InvoiceID INTEGER,Item INTEGER,"
237: + "ProductID INTEGER,Quantity INTEGER,Cost DECIMAL,"
238: + "PRIMARY KEY(InvoiceID,Item), "
239: + "FOREIGN KEY (InvoiceId) REFERENCES "
240: + "Invoice (ID) ON DELETE CASCADE, FOREIGN KEY (ProductId) "
241: + "REFERENCES Product(ID) ON DELETE CASCADE);" };
242:
243: for (int i = 0; i < demo.length; i++) {
244:
245: // drop table may fail
246: try {
247: sStatement.execute(demo[i]);
248: } catch (SQLException e) {
249: ;
250: }
251: }
252: }
253:
254: /**
255: * Method declaration
256: *
257: */
258: static String createTestData(Statement sStatement)
259: throws SQLException {
260:
261: String[] name = { "White", "Karsen", "Smith", "Ringer", "May",
262: "King", "Fuller", "Miller", "Ott", "Sommer",
263: "Schneider", "Steel", "Peterson", "Heiniger", "Clancy" };
264: String[] firstname = { "Mary", "James", "Anne", "George",
265: "Sylvia", "Robert", "Janet", "Michael", "Andrew",
266: "Bill", "Susanne", "Laura", "Bob", "Julia", "John" };
267: String[] street = { "Upland Pl.", "College Av.", "- 20th Ave.",
268: "Seventh Av." };
269: String[] city = { "New York", "Dallas", "Boston", "Chicago",
270: "Seattle", "San Francisco", "Berne", "Oslo", "Paris",
271: "Lyon", "Palo Alto", "Olten" };
272: String[] product = { "Iron", "Ice Tea", "Clock", "Chair",
273: "Telephone", "Shoe" };
274: int max = 50;
275:
276: sStatement.execute("SET REFERENTIAL_INTEGRITY FALSE");
277:
278: for (int i = 0; i < max; i++) {
279: sStatement.execute("INSERT INTO Customer VALUES(" + i
280: + ",'" + random(firstname) + "','" + random(name)
281: + "','" + random(554) + " " + random(street)
282: + "','" + random(city) + "')");
283: sStatement.execute("INSERT INTO Product VALUES(" + i + ",'"
284: + random(product) + " " + random(product) + "',"
285: + (20 + 2 * random(120)) + ")");
286: sStatement.execute("INSERT INTO Invoice VALUES(" + i + ","
287: + random(max) + ",0.0)");
288:
289: for (int j = random(20) + 2; j >= 0; j--) {
290: sStatement.execute("INSERT INTO Item VALUES(" + i + ","
291: + j + "," + random(max) + ","
292: + (1 + random(24)) + ",1.5)");
293: }
294: }
295:
296: sStatement.execute("SET REFERENTIAL_INTEGRITY TRUE");
297: sStatement
298: .execute("UPDATE Product SET Price=ROUND(Price*.1,2)");
299: sStatement
300: .execute("UPDATE Item SET Cost=Cost*"
301: + "SELECT Price FROM Product prod WHERE ProductID=prod.ID");
302: sStatement.execute("UPDATE Invoice SET Total=SELECT SUM(Cost*"
303: + "Quantity) FROM Item WHERE InvoiceID=Invoice.ID");
304:
305: return ("SELECT * FROM Customer");
306: }
307:
308: /**
309: * Method declaration
310: * Redid this file to remove sizing requirements and to make it faster
311: * Speeded it up 10 fold.
312: * @param file
313: * @return
314: */
315: static String readFile(String file) {
316:
317: try {
318: FileReader reader = new FileReader(file);
319: BufferedReader read = new BufferedReader(reader);
320: StringBuffer b = new StringBuffer();
321: String s = null;
322: int count = 0;
323:
324: while ((s = read.readLine()) != null) {
325: count++;
326:
327: b.append(s);
328: b.append('\n');
329: }
330:
331: read.close();
332: reader.close();
333:
334: return b.toString();
335: } catch (IOException e) {
336: return e.getMessage();
337: }
338: }
339:
340: /**
341: * Method declaration
342: *
343: *
344: * @param file
345: * @param text
346: */
347: static void writeFile(String file, String text) {
348:
349: try {
350: FileWriter write = new FileWriter(file);
351:
352: write.write(text.toCharArray());
353: write.close();
354: } catch (IOException e) {
355: e.printStackTrace();
356: }
357: }
358:
359: /**
360: * Method declaration
361: *
362: *
363: * @param sql
364: * @param max
365: *
366: * @return
367: *
368: * @throws SQLException
369: */
370: static long testStatement(Statement sStatement, String sql, int max)
371: throws SQLException {
372:
373: long start = System.currentTimeMillis();
374:
375: if (sql.indexOf('#') == -1) {
376: max = 1;
377: }
378:
379: for (int i = 0; i < max; i++) {
380: String s = sql;
381:
382: while (true) {
383: int j = s.indexOf("#r#");
384:
385: if (j == -1) {
386: break;
387: }
388:
389: s = s.substring(0, j) + ((int) (Math.random() * i))
390: + s.substring(j + 3);
391: }
392:
393: while (true) {
394: int j = s.indexOf('#');
395:
396: if (j == -1) {
397: break;
398: }
399:
400: s = s.substring(0, j) + i + s.substring(j + 1);
401: }
402:
403: sStatement.execute(s);
404: }
405:
406: return (System.currentTimeMillis() - start);
407: }
408:
409: private DatabaseManagerCommon() {
410: }
411: }
|