public long getBookmarkTagId(long bookmarkId, long tagId) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    Cursor cursor =
        mDb.query(
            BookmarkTagTable.TABLE_NAME,
            null,
            BookmarkTagTable.BOOKMARK_ID
                + "="
                + bookmarkId
                + " AND "
                + BookmarkTagTable.TAG_ID
                + "="
                + tagId,
            null,
            null,
            null,
            null);
    if (cursor.moveToFirst()) {
      return cursor.getLong(0);
    }
    return -1;
  }
  public List<Tag> getTags(int sortOrder) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return null;
      }
    }

    String orderBy = null;
    switch (sortOrder) {
      case SORT_DATE_ADDED:
        orderBy = TagsTable.ADDED_DATE + " DESC";
        break;
      case SORT_ALPHABETICAL:
      default:
        orderBy = TagsTable.NAME + " ASC";
        break;
    }
    List<Tag> tags = null;
    Cursor cursor = mDb.query(TagsTable.TABLE_NAME, null, null, null, null, null, orderBy);
    if (cursor != null) {
      tags = new ArrayList<Tag>();
      while (cursor.moveToNext()) {
        long id = cursor.getLong(0);
        String name = cursor.getString(1);
        Tag tag = new Tag(id, name);
        tags.add(tag);
      }
      cursor.close();
    }
    return tags;
  }
  public long getBookmarkId(Integer sura, Integer ayah, int page) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    Cursor cursor =
        mDb.query(
            BookmarksTable.TABLE_NAME,
            null,
            BookmarksTable.PAGE
                + "="
                + page
                + " AND "
                + BookmarksTable.SURA
                + (sura == null ? " IS NULL" : "=" + sura)
                + " AND "
                + BookmarksTable.AYAH
                + (ayah == null ? " IS NULL" : "=" + ayah),
            null,
            null,
            null,
            null);
    if (cursor.moveToFirst()) {
      return cursor.getLong(0);
    }
    return -1;
  }
 public boolean removeBookmark(long bookmarkId) {
   if (mDb == null) {
     open();
     if (mDb == null) {
       return false;
     }
   }
   clearBookmarkTags(bookmarkId);
   return mDb.delete(BookmarksTable.TABLE_NAME, BookmarksTable.ID + "=" + bookmarkId, null) == 1;
 }
  public int clearBookmarkTags(long bookmarkId) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    return mDb.delete(
        BookmarkTagTable.TABLE_NAME, BookmarkTagTable.BOOKMARK_ID + "=" + bookmarkId, null);
  }
 public long addBookmarkIfNotExists(Integer sura, Integer ayah, int page) {
   if (mDb == null) {
     open();
     if (mDb == null) {
       return -1;
     }
   }
   long bookmarkId = getBookmarkId(sura, ayah, page);
   if (bookmarkId < 0) bookmarkId = addBookmark(sura, ayah, page);
   return bookmarkId;
 }
  public long addTag(String name) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    ContentValues values = new ContentValues();
    values.put(TagsTable.NAME, name);
    return mDb.insert(TagsTable.TABLE_NAME, null, values);
  }
  public long tagBookmark(long bookmarkId, long tagId) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    ContentValues values = new ContentValues();
    values.put(BookmarkTagTable.BOOKMARK_ID, bookmarkId);
    values.put(BookmarkTagTable.TAG_ID, tagId);
    return mDb.replace(BookmarkTagTable.TABLE_NAME, null, values);
  }
  public boolean updateTag(long id, String newName) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return false;
      }
    }

    ContentValues values = new ContentValues();
    values.put(TagsTable.ID, id);
    values.put(TagsTable.NAME, newName);
    return 1 == mDb.update(TagsTable.TABLE_NAME, values, TagsTable.ID + "=" + id, null);
  }
  public List<Bookmark> getBookmarks(boolean loadTags, int sortOrder) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return null;
      }
    }

    String orderBy = null;
    switch (sortOrder) {
      case SORT_LOCATION:
        orderBy =
            BookmarksTable.PAGE
                + " ASC, "
                + BookmarksTable.SURA
                + " ASC, "
                + BookmarksTable.AYAH
                + " ASC";
        break;
      case SORT_DATE_ADDED:
      default:
        orderBy = BookmarksTable.ADDED_DATE + " DESC";
        break;
    }
    List<Bookmark> bookmarks = null;
    Cursor cursor = mDb.query(BookmarksTable.TABLE_NAME, null, null, null, null, null, orderBy);
    if (cursor != null) {
      bookmarks = new ArrayList<Bookmark>();
      while (cursor.moveToNext()) {
        long id = cursor.getLong(0);
        Integer sura = cursor.getInt(1);
        Integer ayah = cursor.getInt(2);
        int page = cursor.getInt(3);
        long time = cursor.getLong(4);

        if (sura == 0 || ayah == 0) {
          sura = null;
          ayah = null;
        }

        Bookmark bookmark = new Bookmark(id, sura, ayah, page, time);
        if (loadTags) {
          bookmark.mTags = getBookmarkTags(id);
        }
        bookmarks.add(bookmark);
      }
      cursor.close();
    }
    return bookmarks;
  }
  public long addBookmark(Integer sura, Integer ayah, int page) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return -1;
      }
    }

    ContentValues values = new ContentValues();
    values.put(BookmarksTable.SURA, sura);
    values.put(BookmarksTable.AYAH, ayah);
    values.put(BookmarksTable.PAGE, page);
    return mDb.insert(BookmarksTable.TABLE_NAME, null, values);
  }
  public boolean removeTag(long tagId) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return false;
      }
    }

    boolean removed = mDb.delete(TagsTable.TABLE_NAME, TagsTable.ID + "=" + tagId, null) == 1;
    if (removed) {
      mDb.delete(BookmarkTagTable.TABLE_NAME, BookmarkTagTable.TAG_ID + "=" + tagId, null);
    }

    return removed;
  }
 public void untagBookmark(long bookmarkId, long tagId) {
   if (mDb == null) {
     open();
     if (mDb == null) {
       return;
     }
   }
   mDb.delete(
       BookmarkTagTable.TABLE_NAME,
       BookmarkTagTable.BOOKMARK_ID
           + "="
           + bookmarkId
           + " AND "
           + BookmarkTagTable.TAG_ID
           + "="
           + tagId,
       null);
 }
 public boolean isTagged(long bookmarkId) {
   if (mDb == null) {
     open();
     if (mDb == null) {
       return false;
     }
   }
   Cursor cursor =
       mDb.query(
           BookmarkTagTable.TABLE_NAME,
           null,
           BookmarkTagTable.BOOKMARK_ID + "=" + bookmarkId,
           null,
           null,
           null,
           null);
   return cursor.moveToFirst();
 }
  public void tagBookmark(long bookmarkId, List<Tag> tags) {
    if (mDb == null) {
      open();
      if (mDb == null) {
        return;
      }
    }

    mDb.beginTransaction();
    try {
      for (Tag t : tags) {
        if (t.mId < 0) {
          continue;
        }
        if (t.isChecked()) {
          ContentValues values = new ContentValues();
          values.put(BookmarkTagTable.BOOKMARK_ID, bookmarkId);
          values.put(BookmarkTagTable.TAG_ID, t.mId);
          mDb.replace(BookmarkTagTable.TABLE_NAME, null, values);
        } else {
          mDb.delete(
              BookmarkTagTable.TABLE_NAME,
              BookmarkTagTable.BOOKMARK_ID
                  + "="
                  + bookmarkId
                  + " AND "
                  + BookmarkTagTable.TAG_ID
                  + "="
                  + t.mId,
              null);
        }
      }
    } catch (Exception e) {
      Log.d(TAG, "exception in tagBookmark", e);
    }

    mDb.setTransactionSuccessful();
    mDb.endTransaction();
  }