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 17 package tests.support; 18 19 import java.sql.Connection; 20 import java.sql.Date; 21 import java.sql.SQLException; 22 import java.sql.Statement; 23 import java.sql.Time; 24 25 public 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 fillParentTable(Connection conn)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 fillFKStrictTable(Connection conn)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 fillFKCascadeTable(Connection conn)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 fillSimpleTable1(Connection conn)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 fillSimpleTable3(Connection conn)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 fillSalesPeopleTable(Connection conn)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 fillCustomersTable(Connection conn)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 fillOrdersTable(Connection conn)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 fillTestTable1(Connection conn, int numberOfRecords)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 fillTestTable2(Connection conn, int startID, int endID, long time)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 fillTestTable2(Connection conn, int numberOfRecords)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 fillTestTable4(Connection conn, int numberOfRecords)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 fillTestTable5(Connection conn)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