| /* |
| * Copyright (C) 2007 The Android Open Source Project |
| * |
| * Licensed under the Apache License, Version 2.0 (the "License"); |
| * you may not use this file except in compliance with the License. |
| * You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| |
| package tests.support; |
| |
| import java.sql.Connection; |
| import java.sql.Date; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Time; |
| |
| public class DatabaseCreator { |
| public static final int defaultInt = 2; |
| |
| public static final String defaultString = "string"; |
| |
| public static final String defaultCharacter = "chr"; |
| |
| public static final double defaultDouble = 0.1; |
| |
| public static final String TEST_TABLE1 = "table1"; |
| |
| public static final String TEST_TABLE2 = "table2"; |
| |
| public static final String TEST_TABLE3 = "table3"; |
| |
| public static final String TEST_TABLE4 = "table4"; |
| |
| public static final String SALESPEOPLE_TABLE = "Salespeople"; |
| |
| public static final String CUSTOMERS_TABLE = "Customers"; |
| |
| public static final String ORDERS_TABLE = "Orders"; |
| |
| public static final String PARENT_TABLE = "test_names"; |
| |
| public static final String FKSTRICT_TABLE = "test_strict"; |
| |
| public static final String FKCASCADE_TABLE = "test_cascade"; |
| |
| public static final String TEST_TABLE5 = "test"; |
| |
| public static final String SIMPLE_TABLE1 = "simple_table1"; |
| |
| public static final String SIMPLE_TABLE2 = "simple_table2"; |
| |
| public static final String SIMPLE_TABLE3 = "simple_table3"; |
| |
| public static final String CREATE_TABLE1 = "CREATE TABLE " + TEST_TABLE1 |
| + " (id INTEGER NOT NULL," + " field1 CHAR(100) DEFAULT NULL," |
| + " field2 DECIMAL " //+ defaultInt |
| + " COMMENT 'field2_rem'," + " field3 DECIMAL," + " fkey INTEGER," |
| + " PRIMARY KEY (id) FOREIGN KEY (fkey) REFERENCES " |
| + TEST_TABLE3 + "(fk))"; |
| |
| public static final String CREATE_TABLE2 = "CREATE TABLE " + TEST_TABLE2 |
| + " ( " + "finteger integer NOT NULL, " + "ftext text, " |
| + "fcharacter character (5), " + "fdecimal decimal (5,1), " |
| + "fnumeric numeric (4,1), " + "fsmallint smallint, " |
| + "ffloat float, " + "freal real, " + "fdouble double, " |
| + "fdate date," + " ftime time, PRIMARY KEY (finteger))"; |
| |
| public static final String CREATE_TABLE3 = "CREATE TABLE " + TEST_TABLE3 |
| + " (fk INTEGER NOT NULL," + "" + " PRIMARY KEY (fk))"; |
| |
| public static final String CREATE_TABLE4 = "CREATE TABLE " + TEST_TABLE4 |
| + " (fk INTEGER NOT NULL," + " field1 CHAR(100) NOT NULL," |
| + " PRIMARY KEY (fk))"; |
| |
| public static final String CREATE_TABLE5 = "CREATE TABLE " + TEST_TABLE5 |
| + "( testId INTEGER NOT NULL, testValue CHAR(200))"; |
| |
| public static final String CREATE_TABLE_SALESPEOPLE = "CREATE TABLE " |
| + SALESPEOPLE_TABLE + " (snum integer, sname character (10)," |
| + " city character (10), comm real, PRIMARY KEY (snum))"; |
| |
| public static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE " |
| + CUSTOMERS_TABLE |
| + " (cnum integer, cname character (10), city character (10)," |
| + " rating integer, snum integer, PRIMARY KEY (cnum))"; |
| |
| public static final String CREATE_TABLE_ORDERS = "CREATE TABLE " |
| + ORDERS_TABLE |
| + " (onum integer, amt real, odate date, cnum integer," |
| + " snum integer, PRIMARY KEY (onum))"; |
| |
| public static final String CREATE_TABLE_PARENT = "CREATE TABLE " |
| + PARENT_TABLE + "(id INTEGER NOT NULL, " |
| + "name CHAR(200), PRIMARY KEY(id))"; |
| |
| public static final String CREATE_TABLE_FKSTRICT = "CREATE TABLE " |
| + FKSTRICT_TABLE + "(id INTEGER NOT NULL," + "name_id INTEGER," |
| + "value CHAR(200), PRIMARY KEY(id), " |
| + "CONSTRAINT fk1 FOREIGN KEY (name_id) " + "REFERENCES " |
| + PARENT_TABLE + " (id) " + "ON DELETE RESTRICT " |
| + "ON UPDATE RESTRICT)"; |
| |
| public static final String CREATE_TABLE_FKCASCADE = "CREATE TABLE " |
| + FKCASCADE_TABLE + "(id INTEGER NOT NULL," + "name_id INTEGER," |
| + "value CHAR(200), PRIMARY KEY(id), " |
| + "CONSTRAINT fk2 FOREIGN KEY (name_id) " + "REFERENCES " |
| + PARENT_TABLE + " (id) " + "ON DELETE CASCADE " |
| + "ON UPDATE CASCADE)"; |
| |
| public static final String CREATE_TABLE_SIMPLE1 = "CREATE TABLE " |
| + SIMPLE_TABLE1 + "(id INTEGER NOT NULL," + "speed INTEGER," |
| + "size INTEGER)"; |
| |
| public static final String CREATE_TABLE_SIMPLE2 = "CREATE TABLE " |
| + SIMPLE_TABLE2 + "(id INTEGER NOT NULL," + "speed INTEGER," |
| + "size INTEGER)"; |
| |
| public static final String CREATE_TABLE_SIMPLE3 = "CREATE TABLE " |
| + SIMPLE_TABLE3 + "(id INTEGER NOT NULL," + "speed INTEGER)"; |
| |
| public static final String DROP_TABLE1 = "DROP TABLE " + TEST_TABLE1; |
| |
| public static final String DROP_TABLE2 = "DROP TABLE " + TEST_TABLE2; |
| |
| public static final String DROP_TABLE3 = "DROP TABLE " + TEST_TABLE3; |
| |
| public static final String DROP_TABLE4 = "DROP TABLE " + TEST_TABLE4; |
| |
| public static final String DROP_TABLE5 = "DROP TABLE " + TEST_TABLE5; |
| |
| public static final String DROP_TABLE_CUSTOMERS = "DROP TABLE " |
| + CUSTOMERS_TABLE; |
| |
| public static final String DROP_TABLE_ORDERS = "DROP TABLE " + ORDERS_TABLE; |
| |
| public static final String DROP_TABLE_SALESPEOPLE = "DROP TABLE " |
| + SALESPEOPLE_TABLE; |
| |
| public static final String DROP_TABLE_PARENT = "DROP TABLE " + PARENT_TABLE; |
| |
| public static final String DROP_TABLE_FKSTRICT = "DROP TABLE " |
| + FKSTRICT_TABLE; |
| |
| public static final String DROP_TABLE_FKCASCADE = "DROP TABLE " |
| + FKCASCADE_TABLE; |
| |
| public static final String DROP_TABLE_SIMPLE1 = "DROP TABLE " |
| + SIMPLE_TABLE1; |
| |
| public static final String DROP_TABLE_SIMPLE2 = "DROP TABLE " |
| + SIMPLE_TABLE2; |
| |
| public static final String DROP_TABLE_SIMPLE3 = "DROP TABLE " |
| + SIMPLE_TABLE3; |
| |
| public static final String INSERT_SALESPEOPLE1 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1001, 'Peel', 'London', .12)"; |
| |
| public static final String INSERT_SALESPEOPLE2 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1002, 'Serres', 'SanJose', .13)"; |
| |
| public static final String INSERT_SALESPEOPLE3 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1004, 'Motika', 'London', .11)"; |
| |
| public static final String INSERT_SALESPEOPLE4 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1007, 'Rifkin', 'Barcelona', .15)"; |
| |
| public static final String INSERT_SALESPEOPLE5 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1003, 'Axelrod', 'NewYork', .10)"; |
| |
| public static final String INSERT_SALESPEOPLE6 = " INSERT INTO " |
| + SALESPEOPLE_TABLE + " (snum, sname, city, comm) " |
| + "VALUES (1013, 'Simpson', 'Kasan', .25)"; |
| |
| public static final String INSERT_CUSTOMERS1 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum)" |
| + " VALUES (2001, 'Hoffman', 'London', 100, 1001)"; |
| |
| public static final String INSERT_CUSTOMERS2 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2002, 'Giovanni', 'Rome', 200, 1003)"; |
| |
| public static final String INSERT_CUSTOMERS3 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2003, 'Liu', 'SanJose', 200, 1002)"; |
| |
| public static final String INSERT_CUSTOMERS4 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2004, 'Grass', 'Berlin', 300, 1002)"; |
| |
| public static final String INSERT_CUSTOMERS5 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2006, 'Clemens', 'London', 100, 1001)"; |
| |
| public static final String INSERT_CUSTOMERS6 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2008, 'Cisneros', 'SanJose', 300, 1007)"; |
| |
| public static final String INSERT_CUSTOMERS7 = " INSERT INTO " |
| + CUSTOMERS_TABLE + " (cnum, cname, city, rating, snum) " |
| + "VALUES (2007, 'Pereira', 'Rome', 100, 1004)"; |
| |
| public static final String INSERT_ORDERS1 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3001, 18.69, 10/03/1990, 2008, 1007)"; |
| |
| public static final String INSERT_ORDERS2 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3003, 767.19, 10/03/1990, 2001, 1001)"; |
| |
| public static final String INSERT_ORDERS3 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3002, 1900.10, 10/03/1990, 2007, 1004)"; |
| |
| public static final String INSERT_ORDERS4 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3005, 5160.45, 10/03/1990, 2003, 1002)"; |
| |
| public static final String INSERT_ORDERS5 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3006, 1098.16, 10/03/1990, 2008, 1007)"; |
| |
| public static final String INSERT_ORDERS6 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3009, 1713.23, 10/04/1990, 2002, 1003)"; |
| |
| public static final String INSERT_ORDERS7 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3007, 75.75, 10/04/1990, 2004, 1002)"; |
| |
| public static final String INSERT_ORDERS8 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3008, 4723.01, 10/05/1990, 2006, 1001)"; |
| |
| public static final String INSERT_ORDERS9 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3010, 1309.95, 10/06/1990, 2004, 1002)"; |
| |
| public static final String INSERT_ORDERS10 = " INSERT INTO " + ORDERS_TABLE |
| + " (onum, amt, odate, cnum, snum) " |
| + "VALUES (3011, 9891.88, 10/06/1990, 2006, 1001)"; |
| |
| public static void fillParentTable(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement |
| .execute("INSERT INTO " + PARENT_TABLE + " VALUES(1, 'test1')"); |
| statement.execute("INSERT INTO " + PARENT_TABLE + " VALUES(2,'test2')"); |
| statement |
| .execute("INSERT INTO " + PARENT_TABLE + " VALUES(3, 'test3')"); |
| } |
| |
| public static void fillFKStrictTable(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement.execute("INSERT INTO " + FKSTRICT_TABLE |
| + " VALUES(1, 1, '1')"); |
| statement.execute("INSERT INTO " + FKSTRICT_TABLE |
| + " VALUES(2, 2, '2')"); |
| statement.execute("INSERT INTO " + FKSTRICT_TABLE |
| + " VALUES(3, 1, '3')"); |
| } |
| |
| public static void fillFKCascadeTable(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement.execute("INSERT INTO " + FKCASCADE_TABLE |
| + " VALUES(1, 1, '1')"); |
| statement.execute("INSERT INTO " + FKCASCADE_TABLE |
| + " VALUES(2, 2, '2')"); |
| statement.execute("INSERT INTO " + FKCASCADE_TABLE |
| + " VALUES(3, 1, '3')"); |
| } |
| |
| public static void fillSimpleTable1(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement |
| .execute("INSERT INTO " + SIMPLE_TABLE1 + " VALUES(1, 10, 20)"); |
| statement.execute("INSERT INTO " + SIMPLE_TABLE1 + " VALUES(2, 5, 7)"); |
| } |
| |
| public static void fillSimpleTable3(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(1, 8)"); |
| statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(2, 6)"); |
| statement.execute("INSERT INTO " + SIMPLE_TABLE3 + " VALUES(3, 4)"); |
| } |
| |
| public static void fillSalesPeopleTable(Connection conn) |
| throws SQLException { |
| Statement statement = conn.createStatement(); |
| |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE1); |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE2); |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE3); |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE4); |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE5); |
| statement.execute(DatabaseCreator.INSERT_SALESPEOPLE6); |
| } |
| |
| public static void fillCustomersTable(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS1); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS2); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS3); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS4); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS5); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS6); |
| statement.execute(DatabaseCreator.INSERT_CUSTOMERS7); |
| } |
| |
| public static void fillOrdersTable(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| |
| statement.execute(DatabaseCreator.INSERT_ORDERS1); |
| statement.execute(DatabaseCreator.INSERT_ORDERS2); |
| statement.execute(DatabaseCreator.INSERT_ORDERS3); |
| statement.execute(DatabaseCreator.INSERT_ORDERS4); |
| statement.execute(DatabaseCreator.INSERT_ORDERS5); |
| statement.execute(DatabaseCreator.INSERT_ORDERS6); |
| statement.execute(DatabaseCreator.INSERT_ORDERS7); |
| statement.execute(DatabaseCreator.INSERT_ORDERS8); |
| statement.execute(DatabaseCreator.INSERT_ORDERS9); |
| statement.execute(DatabaseCreator.INSERT_ORDERS10); |
| } |
| |
| public static void fillTestTable1(Connection conn, int numberOfRecords) |
| throws SQLException { |
| Statement statement = conn.createStatement(); |
| for (int id = 0; id < numberOfRecords; id++) { |
| String value = DatabaseCreator.defaultString + id; |
| String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE1 |
| + " (id, field1, field2, field3) VALUES(" + id + ", '" |
| + value + "', " + id + ", " + id + ")"; |
| statement.execute(insertQuery); |
| } |
| } |
| |
| public static void fillTestTable2(Connection conn, int startID, int endID, |
| long time) throws SQLException { |
| Statement statement = conn.createStatement(); |
| for (int id = startID; id <= endID; id++) { |
| double value = id + DatabaseCreator.defaultDouble; |
| String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE2 |
| + " (finteger, ftext, fcharacter, fdecimal, fnumeric," |
| + " fsmallint, ffloat, freal, fdouble, fdate, ftime)" |
| + " VALUES (" + id + ", '" + DatabaseCreator.defaultString |
| + id + "'," + " '" + DatabaseCreator.defaultCharacter + id |
| + "', " + value + ", " + value + "," + value + ", " + value |
| + ", " + value + "," + value + ", '" |
| + new Date(time).toString() + "'," + " '" |
| + new Time(time).toString() + "')"; |
| statement.execute(insertQuery); |
| } |
| } |
| |
| public static void fillTestTable2(Connection conn, int numberOfRecords) |
| throws SQLException { |
| Statement statement = conn.createStatement(); |
| for (int id = 0; id < numberOfRecords; id++) { |
| double value = id + DatabaseCreator.defaultDouble; |
| String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE2 |
| + " (finteger, ftext, fcharacter, fdecimal, fnumeric," |
| + " fsmallint, ffloat, freal, fdouble)" + " VALUES (" + id |
| + ", '" + DatabaseCreator.defaultString + id + "'," + " '" |
| + DatabaseCreator.defaultCharacter + id + "', " + value |
| + ", " + value + "," + value + ", " + value + ", " + value |
| + "," + value + ")"; |
| statement.execute(insertQuery); |
| } |
| } |
| |
| public static void fillTestTable4(Connection conn, int numberOfRecords) |
| throws SQLException { |
| Statement statement = conn.createStatement(); |
| for (int id = 0; id < numberOfRecords; id++) { |
| String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE4 |
| + " (fk, field1) VALUES(" + id + ", \"" |
| + DatabaseCreator.defaultString + id + "\")"; |
| statement.execute(insertQuery); |
| } |
| } |
| |
| public static void fillTestTable5(Connection conn) throws SQLException { |
| Statement statement = conn.createStatement(); |
| statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(1, '0')"); |
| statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(2, '3')"); |
| statement.execute("INSERT INTO " + TEST_TABLE5 + " VALUES(3, '4')"); |
| } |
| } |
