FileDocCategorySizeDatePackage
DeleteFunctionalityTest.javaAPI DocAndroid 1.5 API9359Wed May 06 22:41:06 BST 2009tests.java.sql

DeleteFunctionalityTest

public class DeleteFunctionalityTest extends TestCase

Fields Summary
private static Connection
conn
private static Statement
statement
Constructors Summary
Methods Summary
public voidcreateTestTables()

        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet userTab = meta.getTables(null, null, null, null);

            while (userTab.next()) {
                String tableName = userTab.getString("TABLE_NAME");
                if (tableName.equals(DatabaseCreator.PARENT_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_PARENT);
                } else if (tableName
                        .equals(DatabaseCreator.FKCASCADE_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_FKCASCADE);
                } else if (tableName
                        .equals(DatabaseCreator.FKSTRICT_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_FKSTRICT);
                } else if (tableName
                        .equals(DatabaseCreator.TEST_TABLE5)) {
                    statement.execute(DatabaseCreator.DROP_TABLE5);
                }
            }
            userTab.close();
            statement.execute(DatabaseCreator.CREATE_TABLE_PARENT);
            statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT);
            statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE);
            statement.execute(DatabaseCreator.CREATE_TABLE5);
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
public voiddeleteTestTables()

        try {
            statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE);
            statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT);
            statement.execute(DatabaseCreator.DROP_TABLE_PARENT);
            statement.execute(DatabaseCreator.DROP_TABLE5);
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
public voidsetUp()


         
        super.setUp();
        Support_SQL.loadDriver();
        conn = Support_SQL.getConnection();
        statement = conn.createStatement();
        createTestTables();
        DatabaseCreator.fillParentTable(conn);
public voidtearDown()

        deleteTestTables();
        statement.close();
        conn.close();
        super.tearDown();
public voidtestDelete1()

tests
DeleteFunctionalityTest#testDelete1(). Deletes row with no referencing ones and RESTRICT action

        DatabaseCreator.fillFKStrictTable(conn);
        statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE
                + " WHERE id = 3;");
public voidtestDelete3()

tests
DeleteFunctionalityTest#testDelete3(). Deletes all referencing rows and then deletes referenced one

        statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE
                + " WHERE name_id = 1;");
        statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE
                + " WHERE id = 1;");
public voidtestDelete4()

tests
DeleteFunctionalityTest#testDelete4(). Deletes row with no referencing ones and CASCADE action

        DatabaseCreator.fillFKCascadeTable(conn);
        statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE
                + " WHERE id = 3;");
public voidtestDelete5()

tests
DeleteFunctionalityTest#testDelete5(). Attempts to delete row with referencing ones and CASCADE action - expecting all referencing rows will also be deleted

        statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE
                + " WHERE id = 1;");

        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 1;");
        r.next();
        assertEquals("Should be no rows", 0, r.getInt(1));
        r.close();
public voidtestDelete6()

tests
DeleteFunctionalityTest#testDelete6(). TODO Foreign key functionality is not supported

        DatabaseCreator.fillFKStrictTable(conn);
        statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE
                + " WHERE name_id = ANY (SELECT id FROM "
                + DatabaseCreator.PARENT_TABLE + " WHERE id > 1)");
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.FKSTRICT_TABLE + " WHERE name_id = 1;");
        r.next();
        assertEquals("Should be 2 rows", 2, r.getInt(1));
        r.close();
public voidtestDelete7()

tests
DeleteFunctionalityTest#testDelete7(). Deletes rows using PreparedStatement

        DatabaseCreator.fillTestTable5(conn);
        PreparedStatement stat = conn.prepareStatement("DELETE FROM "
                + DatabaseCreator.TEST_TABLE5 + " WHERE testID = ?");
        stat.setInt(1, 1);
        stat.execute();
        stat.setInt(1, 2);
        stat.execute();
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.TEST_TABLE5 + " WHERE testID < 3 ");
        r.next();
        assertEquals(0, r.getInt(1));
        r.close();
        stat.close();