1package com.xtremelabs.robolectric.shadows;
2
3import android.database.sqlite.SQLiteDatabase;
4import android.database.sqlite.SQLiteDoneException;
5import android.database.sqlite.SQLiteStatement;
6import com.xtremelabs.robolectric.WithTestDefaultsRunner;
7import org.junit.After;
8import org.junit.Before;
9import org.junit.Test;
10import org.junit.runner.RunWith;
11
12import java.sql.ResultSet;
13import java.sql.Statement;
14
15import static com.xtremelabs.robolectric.Robolectric.shadowOf;
16import static org.hamcrest.CoreMatchers.equalTo;
17import static org.junit.Assert.assertThat;
18
19@RunWith(WithTestDefaultsRunner.class)
20public class SQLiteStatementTest {
21    private SQLiteDatabase database;
22
23    @Before
24    public void setUp() throws Exception {
25        database = SQLiteDatabase.openDatabase("path", null, 0);
26        SQLiteStatement createStatement = database.compileStatement("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 ,  UNIQUE (`name`)) ;");
27        createStatement.execute();
28
29        SQLiteStatement createStatement2 = database.compileStatement("CREATE TABLE `countme` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 ,  UNIQUE (`name`)) ;");
30        createStatement2.execute();
31    }
32
33    @After
34    public void tearDown() throws Exception {
35        database.close();
36    }
37
38    @Test
39    public void testExecuteInsert() throws Exception {
40        SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)");
41        insertStatement.bindString(1, "Leg Press");
42        insertStatement.bindLong(2, 0);
43        long pkeyOne = insertStatement.executeInsert();
44        insertStatement.clearBindings();
45        insertStatement.bindString(1, "Bench Press");
46        insertStatement.bindLong(2, 1);
47        long pkeyTwo = insertStatement.executeInsert();
48
49        assertThat(pkeyOne, equalTo(1L));
50        assertThat(pkeyTwo, equalTo(2L));
51
52        Statement statement = shadowOf(database).getConnection().createStatement();
53        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM `routine`");
54        assertThat(resultSet.next(), equalTo(true));
55        assertThat(resultSet.getInt(1), equalTo(2));
56
57        statement = shadowOf(database).getConnection().createStatement();
58        resultSet = statement.executeQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`");
59        assertThat(resultSet.next(), equalTo(true));
60        assertThat(resultSet.getInt(1), equalTo(1));
61        assertThat(resultSet.getString(2), equalTo("Leg Press"));
62        assertThat(resultSet.getInt(3), equalTo(0));
63        assertThat(resultSet.next(), equalTo(true));
64        assertThat(resultSet.getLong(1), equalTo(2L));
65        assertThat(resultSet.getString(2), equalTo("Bench Press"));
66        assertThat(resultSet.getInt(3), equalTo(1));
67    }
68
69    @Test
70    public void simpleQueryTest() throws Exception {
71
72        SQLiteStatement stmt = database.compileStatement("SELECT count(*) FROM `countme`");
73        assertThat(stmt.simpleQueryForLong(), equalTo(0L));
74        assertThat(stmt.simpleQueryForString(), equalTo("0"));
75
76        SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `countme` (`name` ,`lastUsed` ) VALUES (?,?)");
77        insertStatement.bindString(1, "Leg Press");
78        insertStatement.bindLong(2, 0);
79        insertStatement.executeInsert();
80        assertThat(stmt.simpleQueryForLong(), equalTo(1L));
81        assertThat(stmt.simpleQueryForString(), equalTo("1"));
82        insertStatement.bindString(1, "Bench Press");
83        insertStatement.bindLong(2, 1);
84        insertStatement.executeInsert();
85        assertThat(stmt.simpleQueryForLong(), equalTo(2L));
86        assertThat(stmt.simpleQueryForString(), equalTo("2"));
87    }
88
89    @Test(expected = SQLiteDoneException.class)
90    public void simpleQueryForStringThrowsSQLiteDoneExceptionTest() throws Exception {
91        //throw SQLiteDOneException if no rows returned.
92        SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'");
93
94        assertThat(stmt.simpleQueryForString(), equalTo("0"));
95    }
96
97    @Test(expected = SQLiteDoneException.class)
98    public void simpleQueryForLongThrowsSQLiteDoneExceptionTest() throws Exception {
99        //throw SQLiteDOneException if no rows returned.
100        SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'");
101        assertThat(stmt.simpleQueryForLong(), equalTo(0L));
102
103    }
104}
105