Home | History | Annotate | Download | only in shadows 
      1 package org.robolectric.shadows;
      2 
      3 import static org.assertj.core.api.Assertions.assertThat;
      4 import static org.junit.Assert.fail;
      5 
      6 import android.database.Cursor;
      7 import android.database.sqlite.SQLiteDatabase;
      8 import android.database.sqlite.SQLiteDoneException;
      9 import android.database.sqlite.SQLiteStatement;
     10 import java.io.File;
     11 import org.junit.After;
     12 import org.junit.Before;
     13 import org.junit.Test;
     14 import org.junit.runner.RunWith;
     15 import org.robolectric.RobolectricTestRunner;
     16 import org.robolectric.RuntimeEnvironment;
     17 
     18 @RunWith(RobolectricTestRunner.class)
     19 public class SQLiteStatementTest {
     20   private SQLiteDatabase database;
     21 
     22   @Before
     23   public void setUp() throws Exception {
     24     final File databasePath = RuntimeEnvironment.application.getDatabasePath("path");
     25     databasePath.getParentFile().mkdirs();
     26 
     27     database = SQLiteDatabase.openOrCreateDatabase(databasePath.getPath(), null);
     28     SQLiteStatement createStatement = database.compileStatement("CREATE TABLE `routine` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 ,  UNIQUE (`name`)) ;");
     29     createStatement.execute();
     30 
     31     SQLiteStatement createStatement2 = database.compileStatement("CREATE TABLE `countme` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `name` VARCHAR , `lastUsed` INTEGER DEFAULT 0 ,  UNIQUE (`name`)) ;");
     32     createStatement2.execute();
     33   }
     34 
     35   @After
     36   public void tearDown() throws Exception {
     37     database.close();
     38   }
     39 
     40   @Test
     41   public void testExecuteInsert() throws Exception {
     42     SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name` ,`lastUsed` ) VALUES (?,?)");
     43     insertStatement.bindString(1, "Leg Press");
     44     insertStatement.bindLong(2, 0);
     45     long pkeyOne = insertStatement.executeInsert();
     46     insertStatement.clearBindings();
     47     insertStatement.bindString(1, "Bench Press");
     48     insertStatement.bindLong(2, 1);
     49     long pkeyTwo = insertStatement.executeInsert();
     50 
     51     assertThat(pkeyOne).isEqualTo(1L);
     52     assertThat(pkeyTwo).isEqualTo(2L);
     53 
     54     Cursor dataCursor = database.rawQuery("SELECT COUNT(*) FROM `routine`", null);
     55     assertThat(dataCursor.moveToFirst()).isTrue();
     56     assertThat(dataCursor.getInt(0)).isEqualTo(2);
     57     dataCursor.close();
     58 
     59     dataCursor = database.rawQuery("SELECT `id`, `name` ,`lastUsed` FROM `routine`", null);
     60     assertThat(dataCursor.moveToNext()).isTrue();
     61     assertThat(dataCursor.getInt(0)).isEqualTo(1);
     62     assertThat(dataCursor.getString(1)).isEqualTo("Leg Press");
     63     assertThat(dataCursor.getInt(2)).isEqualTo(0);
     64     assertThat(dataCursor.moveToNext()).isTrue();
     65     assertThat(dataCursor.getLong(0)).isEqualTo(2L);
     66     assertThat(dataCursor.getString(1)).isEqualTo("Bench Press");
     67     assertThat(dataCursor.getInt(2)).isEqualTo(1);
     68     dataCursor.close();
     69   }
     70 
     71   @Test
     72   public void testExecuteInsertShouldCloseGeneratedKeysResultSet() throws Exception {
     73     // NOTE:
     74     // As a side-effect we will get "database locked" exception
     75     // on rollback if generatedKeys wasn't closed
     76     //
     77     // Don't know how suitable to use Mockito here, but
     78     // it will be a little bit simpler to test ShadowSQLiteStatement
     79     // if actualDBStatement will be mocked
     80     database.beginTransaction();
     81     try {
     82       SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` " +
     83           "(`name` ,`lastUsed`) VALUES ('test',0)");
     84       try {
     85         insertStatement.executeInsert();
     86       } finally {
     87         insertStatement.close();
     88       }
     89     } finally {
     90       database.endTransaction();
     91     }
     92   }
     93 
     94   @Test
     95   public void testExecuteUpdateDelete() throws Exception {
     96 
     97     SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)");
     98     insertStatement.bindString(1, "Hand Press");
     99     long pkeyOne = insertStatement.executeInsert();
    100     assertThat(pkeyOne).isEqualTo(1);
    101 
    102     SQLiteStatement updateStatement = database.compileStatement("UPDATE `routine` SET `name`=? WHERE `id`=?");
    103     updateStatement.bindString(1, "Head Press");
    104     updateStatement.bindLong(2, pkeyOne);
    105     assertThat(updateStatement.executeUpdateDelete()).isEqualTo(1);
    106 
    107     Cursor dataCursor = database.rawQuery("SELECT `name` FROM `routine`", null);
    108     assertThat(dataCursor.moveToNext()).isTrue();
    109     assertThat(dataCursor.getString(0)).isEqualTo("Head Press");
    110   }
    111 
    112   @Test
    113   public void simpleQueryTest() throws Exception {
    114 
    115     SQLiteStatement stmt = database.compileStatement("SELECT count(*) FROM `countme`");
    116     assertThat(stmt.simpleQueryForLong()).isEqualTo(0L);
    117     assertThat(stmt.simpleQueryForString()).isEqualTo("0");
    118 
    119     SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `countme` (`name` ,`lastUsed` ) VALUES (?,?)");
    120     insertStatement.bindString(1, "Leg Press");
    121     insertStatement.bindLong(2, 0);
    122     insertStatement.executeInsert();
    123     assertThat(stmt.simpleQueryForLong()).isEqualTo(1L);
    124     assertThat(stmt.simpleQueryForString()).isEqualTo("1");
    125     insertStatement.bindString(1, "Bench Press");
    126     insertStatement.bindLong(2, 1);
    127     insertStatement.executeInsert();
    128     assertThat(stmt.simpleQueryForLong()).isEqualTo(2L);
    129     assertThat(stmt.simpleQueryForString()).isEqualTo("2");
    130   }
    131 
    132   @Test(expected = SQLiteDoneException.class)
    133   public void simpleQueryForStringThrowsSQLiteDoneExceptionTest() throws Exception {
    134     //throw SQLiteDOneException if no rows returned.
    135     SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'");
    136 
    137     assertThat(stmt.simpleQueryForString()).isEqualTo("0");
    138   }
    139 
    140   @Test(expected = SQLiteDoneException.class)
    141   public void simpleQueryForLongThrowsSQLiteDoneExceptionTest() throws Exception {
    142     //throw SQLiteDOneException if no rows returned.
    143     SQLiteStatement stmt = database.compileStatement("SELECT * FROM `countme` where `name`= 'cessationoftime'");
    144     stmt.simpleQueryForLong();
    145   }
    146 
    147   @Test
    148   public void testCloseShouldCloseUnderlyingPreparedStatement() throws Exception {
    149     SQLiteStatement insertStatement = database.compileStatement("INSERT INTO `routine` (`name`) VALUES (?)");
    150     insertStatement.bindString(1, "Hand Press");
    151     insertStatement.close();
    152     try {
    153       insertStatement.executeInsert();
    154       fail();
    155     } catch (Exception e) {
    156       assertThat(e).isInstanceOf(IllegalStateException.class);
    157     }
    158   }
    159 }
    160