Codes for SQLDelight Blog

PostsDatabaseHelper.java

public class PostsDatabaseHelper extends SQLiteOpenHelper { 
  @Override
  public void onCreate(SQLiteDatabase db) {
    String CREATE_POSTS_TABLE = "CREATE TABLE " + TABLE_POSTS +
      "(" +
          KEY_POST_ID + " INTEGER PRIMARY KEY," + // Define a primary key
          KEY_POST_USER_ID_FK + " INTEGER REFERENCES " + TABLE_USERS + "," + // Define a foreign key
          KEY_POST_TEXT + " TEXT" +
      ")";

    db.execSQL(CREATE_POSTS_TABLE);
  }

public long addOrUpdateUser(User user) {
  // The database connection is cached so it's not expensive to call getWriteableDatabase() multiple times.
  SQLiteDatabase db = getWritableDatabase();
  long userId = -1;

  db.beginTransaction();
  try {
    ContentValues values = new ContentValues();
    values.put(KEY_USER_NAME, user.userName);
    values.put(KEY_USER_PROFILE_PICTURE_URL, user.profilePictureUrl);

    // First try to update the user in case the user already exists in the database
    // This assumes userNames are unique
    int rows = db.update(TABLE_USERS, values, KEY_USER_NAME + "= ?", new String[]{user.userName});

    // Check if update succeeded
    if (rows == 1) {
      // Get the primary key of the user we just updated
      String usersSelectQuery = String.format("SELECT %s FROM %s WHERE %s = ?",
              KEY_USER_ID, TABLE_USERS, KEY_USER_NAME);
      Cursor cursor = db.rawQuery(usersSelectQuery, new String[]{String.valueOf(user.userName)});
      try {
        if (cursor.moveToFirst()) {
          userId = cursor.getInt(0);
          db.setTransactionSuccessful();
        }
      } finally {
        if (cursor != null && !cursor.isClosed()) {
          cursor.close();
        }
      }
    } else {
      // user with this userName did not already exist, so insert new user
      userId = db.insertOrThrow(TABLE_USERS, null, values);
      db.setTransactionSuccessful();
    }
  } catch (Exception e) {
    Log.d(TAG, "Error while trying to add or update user");
  } finally {
    db.endTransaction();
  }
  return userId;
}