1/*
2 * Copyright (C) 2007 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *     http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package tests.support;
18
19import java.sql.Connection;
20import java.sql.Date;
21import java.sql.SQLException;
22import java.sql.Statement;
23import java.sql.Time;
24
25public class DatabaseCreator {
26    public static final int defaultInt = 2;
27
28    public static final String defaultString = "string";
29
30    public static final String defaultCharacter = "chr";
31
32    public static final double defaultDouble = 0.1;
33
34    public static final String TEST_TABLE1 = "table1";
35
36    public static final String TEST_TABLE2 = "table2";
37
38    public static final String TEST_TABLE3 = "table3";
39
40    public static final String TEST_TABLE4 = "table4";
41
42    public static final String SALESPEOPLE_TABLE = "Salespeople";
43
44    public static final String CUSTOMERS_TABLE = "Customers";
45
46    public static final String ORDERS_TABLE = "Orders";
47
48    public static final String PARENT_TABLE = "test_names";
49
50    public static final String FKSTRICT_TABLE = "test_strict";
51
52    public static final String FKCASCADE_TABLE = "test_cascade";
53
54    public static final String TEST_TABLE5 = "test";
55
56    public static final String SIMPLE_TABLE1 = "simple_table1";
57
58    public static final String SIMPLE_TABLE2 = "simple_table2";
59
60    public static final String SIMPLE_TABLE3 = "simple_table3";
61
62    public static final String CREATE_TABLE1 = "CREATE TABLE " + TEST_TABLE1
63            + " (id INTEGER NOT NULL," + " field1 CHAR(100) DEFAULT NULL,"
64            + " field2 DECIMAL " //+ defaultInt
65            + " COMMENT 'field2_rem'," + " field3 DECIMAL," + " fkey INTEGER,"
66            + " PRIMARY KEY (id) FOREIGN KEY (fkey) REFERENCES "
67            + TEST_TABLE3 + "(fk))";
68
69    public static final String CREATE_TABLE2 = "CREATE TABLE " + TEST_TABLE2
70            + " ( " + "finteger integer NOT NULL, " + "ftext text, "
71            + "fcharacter character (5), " + "fdecimal decimal (5,1), "
72            + "fnumeric numeric (4,1), " + "fsmallint smallint, "
73            + "ffloat float, " + "freal real, " + "fdouble double, "
74            + "fdate date," + " ftime time, PRIMARY KEY (finteger))";
75
76    public static final String CREATE_TABLE3 = "CREATE TABLE " + TEST_TABLE3
77            + " (fk INTEGER NOT NULL," + "" + " PRIMARY KEY (fk))";
78
79    public static final String CREATE_TABLE4 = "CREATE TABLE " + TEST_TABLE4
80            + " (fk INTEGER NOT NULL," + " field1 CHAR(100) NOT NULL,"
81            + " PRIMARY KEY (fk))";
82
83    public static final String CREATE_TABLE5 = "CREATE TABLE " + TEST_TABLE5
84            + "( testId INTEGER NOT NULL, testValue CHAR(200))";
85
86    public static final String CREATE_TABLE_SALESPEOPLE = "CREATE TABLE "
87            + SALESPEOPLE_TABLE + " (snum integer, sname character (10),"
88            + " city character (10), comm real, PRIMARY KEY (snum))";
89
90    public static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE "
91            + CUSTOMERS_TABLE
92            + " (cnum integer, cname character (10), city character (10),"
93            + " rating integer, snum integer, PRIMARY KEY (cnum))";
94
95    public static final String CREATE_TABLE_ORDERS = "CREATE TABLE "
96            + ORDERS_TABLE
97            + " (onum integer, amt real, odate date, cnum integer,"
98            + " snum integer, PRIMARY KEY (onum))";
99
100    public static final String CREATE_TABLE_PARENT = "CREATE TABLE "
101            + PARENT_TABLE + "(id INTEGER NOT NULL, "
102            + "name CHAR(200), PRIMARY KEY(id))";
103
104    public static final String CREATE_TABLE_FKSTRICT = "CREATE TABLE "
105            + FKSTRICT_TABLE + "(id INTEGER NOT NULL," + "name_id INTEGER,"
106            + "value CHAR(200), PRIMARY KEY(id), "
107            + "CONSTRAINT fk1 FOREIGN KEY (name_id) " + "REFERENCES "
108            + PARENT_TABLE + " (id) " + "ON DELETE RESTRICT "
109            + "ON UPDATE RESTRICT)";
110
111    public static final String CREATE_TABLE_FKCASCADE = "CREATE TABLE "
112            + FKCASCADE_TABLE + "(id INTEGER NOT NULL," + "name_id INTEGER,"
113            + "value CHAR(200), PRIMARY KEY(id), "
114            + "CONSTRAINT fk2 FOREIGN KEY (name_id) " + "REFERENCES "
115            + PARENT_TABLE + " (id) " + "ON DELETE CASCADE "
116            + "ON UPDATE CASCADE)";
117
118    public static final String CREATE_TABLE_SIMPLE1 = "CREATE TABLE "
119            + SIMPLE_TABLE1 + "(id INTEGER NOT NULL," + "speed INTEGER,"
120            + "size INTEGER)";
121
122    public static final String CREATE_TABLE_SIMPLE2 = "CREATE TABLE "
123            + SIMPLE_TABLE2 + "(id INTEGER NOT NULL," + "speed INTEGER,"
124            + "size INTEGER)";
125
126    public static final String CREATE_TABLE_SIMPLE3 = "CREATE TABLE "
127            + SIMPLE_TABLE3 + "(id INTEGER NOT NULL," + "speed INTEGER)";
128
129    public static final String DROP_TABLE1 = "DROP TABLE " + TEST_TABLE1;
130
131    public static final String DROP_TABLE2 = "DROP TABLE " + TEST_TABLE2;
132
133    public static final String DROP_TABLE3 = "DROP TABLE " + TEST_TABLE3;
134
135    public static final String DROP_TABLE4 = "DROP TABLE " + TEST_TABLE4;
136
137    public static final String DROP_TABLE5 = "DROP TABLE " + TEST_TABLE5;
138
139    public static final String DROP_TABLE_CUSTOMERS = "DROP TABLE "
140            + CUSTOMERS_TABLE;
141
142    public static final String DROP_TABLE_ORDERS = "DROP TABLE " + ORDERS_TABLE;
143
144    public static final String DROP_TABLE_SALESPEOPLE = "DROP TABLE "
145            + SALESPEOPLE_TABLE;
146
147    public static final String DROP_TABLE_PARENT = "DROP TABLE " + PARENT_TABLE;
148
149    public static final String DROP_TABLE_FKSTRICT = "DROP TABLE "
150            + FKSTRICT_TABLE;
151
152    public static final String DROP_TABLE_FKCASCADE = "DROP TABLE "
153            + FKCASCADE_TABLE;
154
155    public static final String DROP_TABLE_SIMPLE1 = "DROP TABLE "
156            + SIMPLE_TABLE1;
157
158    public static final String DROP_TABLE_SIMPLE2 = "DROP TABLE "
159            + SIMPLE_TABLE2;
160
161    public static final String DROP_TABLE_SIMPLE3 = "DROP TABLE "
162            + SIMPLE_TABLE3;
163
164    public static final String INSERT_SALESPEOPLE1 = " INSERT INTO "
165            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
166            + "VALUES (1001, 'Peel', 'London', .12)";
167
168    public static final String INSERT_SALESPEOPLE2 = " INSERT INTO "
169            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
170            + "VALUES (1002, 'Serres', 'SanJose', .13)";
171
172    public static final String INSERT_SALESPEOPLE3 = " INSERT INTO "
173            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
174            + "VALUES (1004, 'Motika', 'London', .11)";
175
176    public static final String INSERT_SALESPEOPLE4 = " INSERT INTO "
177            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
178            + "VALUES (1007, 'Rifkin', 'Barcelona', .15)";
179
180    public static final String INSERT_SALESPEOPLE5 = " INSERT INTO "
181            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
182            + "VALUES (1003, 'Axelrod', 'NewYork', .10)";
183
184    public static final String INSERT_SALESPEOPLE6 = " INSERT INTO "
185            + SALESPEOPLE_TABLE + " (snum, sname, city, comm) "
186            + "VALUES (1013, 'Simpson', 'Kasan', .25)";
187
188    public static final String INSERT_CUSTOMERS1 = " INSERT INTO "
189            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum)"
190            + " VALUES (2001, 'Hoffman', 'London', 100, 1001)";
191
192    public static final String INSERT_CUSTOMERS2 = " INSERT INTO "
193            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
194            + "VALUES (2002, 'Giovanni', 'Rome', 200, 1003)";
195
196    public static final String INSERT_CUSTOMERS3 = " INSERT INTO "
197            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
198            + "VALUES (2003, 'Liu', 'SanJose', 200, 1002)";
199
200    public static final String INSERT_CUSTOMERS4 = " INSERT INTO "
201            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
202            + "VALUES (2004, 'Grass', 'Berlin', 300, 1002)";
203
204    public static final String INSERT_CUSTOMERS5 = " INSERT INTO "
205            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
206            + "VALUES (2006, 'Clemens', 'London', 100, 1001)";
207
208    public static final String INSERT_CUSTOMERS6 = " INSERT INTO "
209            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
210            + "VALUES (2008, 'Cisneros', 'SanJose', 300, 1007)";
211
212    public static final String INSERT_CUSTOMERS7 = " INSERT INTO "
213            + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) "
214            + "VALUES (2007, 'Pereira', 'Rome', 100, 1004)";
215
216    public static final String INSERT_ORDERS1 = " INSERT INTO " + ORDERS_TABLE
217            + " (onum, amt, odate, cnum, snum) "
218            + "VALUES (3001, 18.69, 10/03/1990, 2008, 1007)";
219
220    public static final String INSERT_ORDERS2 = " INSERT INTO " + ORDERS_TABLE
221            + " (onum, amt, odate, cnum, snum) "
222            + "VALUES (3003, 767.19, 10/03/1990, 2001, 1001)";
223
224    public static final String INSERT_ORDERS3 = " INSERT INTO " + ORDERS_TABLE
225            + " (onum, amt, odate, cnum, snum) "
226            + "VALUES (3002, 1900.10, 10/03/1990, 2007, 1004)";
227
228    public static final String INSERT_ORDERS4 = " INSERT INTO " + ORDERS_TABLE
229            + " (onum, amt, odate, cnum, snum) "
230            + "VALUES (3005, 5160.45, 10/03/1990, 2003, 1002)";
231
232    public static final String INSERT_ORDERS5 = " INSERT INTO " + ORDERS_TABLE
233            + " (onum, amt, odate, cnum, snum) "
234            + "VALUES (3006, 1098.16, 10/03/1990, 2008, 1007)";
235
236    public static final String INSERT_ORDERS6 = " INSERT INTO " + ORDERS_TABLE
237            + " (onum, amt, odate, cnum, snum) "
238            + "VALUES (3009, 1713.23, 10/04/1990, 2002, 1003)";
239
240    public static final String INSERT_ORDERS7 = " INSERT INTO " + ORDERS_TABLE
241            + " (onum, amt, odate, cnum, snum) "
242            + "VALUES (3007, 75.75, 10/04/1990, 2004, 1002)";
243
244    public static final String INSERT_ORDERS8 = " INSERT INTO " + ORDERS_TABLE
245            + " (onum, amt, odate, cnum, snum) "
246            + "VALUES (3008, 4723.01, 10/05/1990, 2006, 1001)";
247
248    public static final String INSERT_ORDERS9 = " INSERT INTO " + ORDERS_TABLE
249            + " (onum, amt, odate, cnum, snum) "
250            + "VALUES (3010, 1309.95, 10/06/1990, 2004, 1002)";
251
252    public static final String INSERT_ORDERS10 = " INSERT INTO " + ORDERS_TABLE
253            + " (onum, amt, odate, cnum, snum) "
254            + "VALUES (3011, 9891.88, 10/06/1990, 2006, 1001)";
255
256    public static void fillParentTable(Connection conn) throws SQLException {
257        Statement statement = conn.createStatement();
258        statement
259                .execute("INSERT INTO " + PARENT_TABLE + " VALUES(1, 'test1')");
260        statement.execute("INSERT INTO " + PARENT_TABLE + " VALUES(2,'test2')");
261        statement
262                .execute("INSERT INTO " + PARENT_TABLE + " VALUES(3, 'test3')");
263    }
264
265    public static void fillFKStrictTable(Connection conn) throws SQLException {
266        Statement statement = conn.createStatement();
267        statement.execute("INSERT INTO " + FKSTRICT_TABLE
268                + " VALUES(1, 1, '1')");
269        statement.execute("INSERT INTO " + FKSTRICT_TABLE
270                + " VALUES(2, 2, '2')");
271        statement.execute("INSERT INTO " + FKSTRICT_TABLE
272                + " VALUES(3, 1, '3')");
273    }
274
275    public static void fillFKCascadeTable(Connection conn) throws SQLException {
276        Statement statement = conn.createStatement();
277        statement.execute("INSERT INTO " + FKCASCADE_TABLE
278                + " VALUES(1, 1, '1')");
279        statement.execute("INSERT INTO " + FKCASCADE_TABLE
280                + " VALUES(2, 2, '2')");
281        statement.execute("INSERT INTO " + FKCASCADE_TABLE
282                + " VALUES(3, 1, '3')");
283    }
284
285    public static void fillSimpleTable1(Connection conn) throws SQLException {
286        Statement statement = conn.createStatement();
287        statement
288                .execute("INSERT INTO " + SIMPLE_TABLE1 + " VALUES(1, 10, 20)");
289        statement.execute("INSERT INTO " + SIMPLE_TABLE1 + " VALUES(2, 5, 7)");
290    }
291
292    public static void fillSimpleTable3(Connection conn) throws SQLException {
293        Statement statement = conn.createStatement();
294        statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(1, 8)");
295        statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(2, 6)");
296        statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(3, 4)");
297    }
298
299    public static void fillSalesPeopleTable(Connection conn)
300            throws SQLException {
301        Statement statement = conn.createStatement();
302
303        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE1);
304        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE2);
305        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE3);
306        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE4);
307        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE5);
308        statement.execute(DatabaseCreator.INSERT_SALESPEOPLE6);
309    }
310
311    public static void fillCustomersTable(Connection conn) throws SQLException {
312        Statement statement = conn.createStatement();
313
314        statement.execute(DatabaseCreator.INSERT_CUSTOMERS1);
315        statement.execute(DatabaseCreator.INSERT_CUSTOMERS2);
316        statement.execute(DatabaseCreator.INSERT_CUSTOMERS3);
317        statement.execute(DatabaseCreator.INSERT_CUSTOMERS4);
318        statement.execute(DatabaseCreator.INSERT_CUSTOMERS5);
319        statement.execute(DatabaseCreator.INSERT_CUSTOMERS6);
320        statement.execute(DatabaseCreator.INSERT_CUSTOMERS7);
321    }
322
323    public static void fillOrdersTable(Connection conn) throws SQLException {
324        Statement statement = conn.createStatement();
325
326        statement.execute(DatabaseCreator.INSERT_ORDERS1);
327        statement.execute(DatabaseCreator.INSERT_ORDERS2);
328        statement.execute(DatabaseCreator.INSERT_ORDERS3);
329        statement.execute(DatabaseCreator.INSERT_ORDERS4);
330        statement.execute(DatabaseCreator.INSERT_ORDERS5);
331        statement.execute(DatabaseCreator.INSERT_ORDERS6);
332        statement.execute(DatabaseCreator.INSERT_ORDERS7);
333        statement.execute(DatabaseCreator.INSERT_ORDERS8);
334        statement.execute(DatabaseCreator.INSERT_ORDERS9);
335        statement.execute(DatabaseCreator.INSERT_ORDERS10);
336    }
337
338    public static void fillTestTable1(Connection conn, int numberOfRecords)
339            throws SQLException {
340        Statement statement = conn.createStatement();
341        for (int id = 0; id < numberOfRecords; id++) {
342            String value = DatabaseCreator.defaultString + id;
343            String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE1
344                    + " (id, field1, field2, field3) VALUES(" + id + ", '"
345                    + value + "', " + id + ", " + id + ")";
346            statement.execute(insertQuery);
347        }
348    }
349
350    public static void fillTestTable2(Connection conn, int startID, int endID,
351            long time) throws SQLException {
352        Statement statement = conn.createStatement();
353        for (int id = startID; id <= endID; id++) {
354            double value = id + DatabaseCreator.defaultDouble;
355            String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE2
356                    + " (finteger, ftext, fcharacter, fdecimal, fnumeric,"
357                    + " fsmallint, ffloat, freal, fdouble, fdate, ftime)"
358                    + " VALUES (" + id + ", '" + DatabaseCreator.defaultString
359                    + id + "'," + " '" + DatabaseCreator.defaultCharacter + id
360                    + "', " + value + ", " + value + "," + value + ", " + value
361                    + ", " + value + "," + value + ", '"
362                    + new Date(time).toString() + "'," + " '"
363                    + new Time(time).toString() + "')";
364            statement.execute(insertQuery);
365        }
366    }
367
368    public static void fillTestTable2(Connection conn, int numberOfRecords)
369            throws SQLException {
370        Statement statement = conn.createStatement();
371        for (int id = 0; id < numberOfRecords; id++) {
372            double value = id + DatabaseCreator.defaultDouble;
373            String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE2
374                    + " (finteger, ftext, fcharacter, fdecimal, fnumeric,"
375                    + " fsmallint, ffloat, freal, fdouble)" + " VALUES (" + id
376                    + ", '" + DatabaseCreator.defaultString + id + "'," + " '"
377                    + DatabaseCreator.defaultCharacter + id + "', " + value
378                    + ", " + value + "," + value + ", " + value + ", " + value
379                    + "," + value + ")";
380            statement.execute(insertQuery);
381        }
382    }
383
384    public static void fillTestTable4(Connection conn, int numberOfRecords)
385            throws SQLException {
386        Statement statement = conn.createStatement();
387        for (int id = 0; id < numberOfRecords; id++) {
388            String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE4
389                    + " (fk, field1) VALUES(" + id + ", \""
390                    + DatabaseCreator.defaultString + id + "\")";
391            statement.execute(insertQuery);
392        }
393    }
394
395    public static void fillTestTable5(Connection conn) throws SQLException {
396        Statement statement = conn.createStatement();
397        statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(1, '0')");
398        statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(2, '3')");
399        statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(3, '4')");
400    }
401}
402