net.potterpcs.recipebook.RecipeData.java Source code

Java tutorial

Introduction

Here is the source code for net.potterpcs.recipebook.RecipeData.java

Source

/*
 *    Copyright 2012 Michael Potter
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * 
 */

/*
 * RecipeData.java - Database access layer and import/export functionality. 
 */

package net.potterpcs.recipebook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.util.ArrayList;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.text.TextUtils;

public class RecipeData {

    //   private static final String TAG = "RecipeData";

    // Database version and filename
    static final int DB_VERSION = 9;
    static final String DB_FILENAME = "recipebook.db";

    // Lock object for synchronization
    static final Object DB_LOCK = new Object();

    // Definitions for the recipe table
    static final String RECIPES_TABLE = "recipes";
    public static final String RT_ID = "_id";
    public static final String RT_NAME = "name";
    public static final String RT_DESCRIPTION = "description";
    public static final String RT_RATING = "rating";
    public static final String RT_CREATOR = "creator";
    public static final String RT_DATE = "date";
    public static final String RT_SERVING = "serving";
    public static final String RT_TIME = "time";
    public static final String RT_PHOTO = "photo";
    public static final String[] RECIPES_FIELDS = { RT_ID, RT_NAME, RT_DESCRIPTION, RT_RATING, RT_CREATOR, RT_DATE,
            RT_SERVING, RT_TIME, RT_PHOTO };

    // Definitions for the ingredients table
    // TODO Add ingredient photos?
    static final String INGREDIENTS_TABLE = "ingredients";
    public static final String IT_ID = "_id";
    public static final String IT_NAME = "name";
    public static final String IT_RECIPE_ID = "recipe_id";
    public static final String[] INGREDIENTS_FIELDS = { IT_ID, IT_NAME };

    // Definitions for the directions table
    static final String DIRECTIONS_TABLE = "directions";
    public static final String DT_ID = "_id";
    public static final String DT_STEP = "step";
    public static final String DT_SEQUENCE = "sequence";
    public static final String DT_PHOTO = "photo";
    public static final String DT_RECIPE_ID = "recipe_id";
    public static final String[] DIRECTIONS_FIELDS = { DT_ID, DT_STEP, DT_SEQUENCE, DT_PHOTO };

    // Definitions for the tags table
    static final String TAGS_TABLE = "tags";
    public static final String TT_ID = "_id";
    public static final String TT_TAG = "tag";
    public static final String TT_RECIPE_ID = "recipe_id";
    public static final String[] TAGS_FIELDS = { TT_ID, TT_TAG };

    // Definitions for the cache table
    static final String CACHE_TABLE = "imagecache";
    public static final String CT_ID = "_id";
    public static final String CT_URI = "uri";
    public static final String CT_CACHED = "cached";
    public static final String[] CACHE_FIELDS = { CT_URI, CT_CACHED };

    public static class Recipe {
        // These don't really need to be private, because it's just a data class
        long id;
        String name;
        String description;
        float rating;
        String creator;
        String date;
        int serving;
        int time;
        String[] ingredients;
        String[] directions;
        String[] directions_photos;
        String[] tags;
        String photo;

        // convert the Recipe to plain text for sharing
        // takes a Context so that it can load resource strings
        public String toText(Context ctx) {
            StringBuilder sb = new StringBuilder();
            Resources res = ctx.getResources();
            sb.append(name + ' ' + res.getString(R.string.rvby) + ' ' + creator + '\n');
            sb.append(description + '\n');
            sb.append(res.getString(R.string.rvserves) + ' ');
            sb.append(serving);
            sb.append('\n' + res.getString(R.string.recipetime) + ' ');
            sb.append(String.format(res.getString(R.string.minutesecond), time / 60, time % 60));
            sb.append("\n\n" + res.getString(R.string.ingredientstext) + '\n');
            for (String ing : ingredients) {
                sb.append("* " + ing + '\n');
            }
            sb.append('\n' + res.getString(R.string.directionstext) + '\n');

            // we need to iterate by index to preserve step numbers
            for (int i = 0; i < directions.length; ++i) {
                // array indices are 0-based, recipes are 1-based
                sb.append(i + 1);
                sb.append(". " + directions[i] + '\n');
            }
            return sb.toString();
        }

        // convert the Recipe to JSON for exporting 
        public String toJSONString() {
            return toJSON().toString();
        }

        // create a JSON object from a Recipe
        public JSONObject toJSON() {
            JSONObject jo = new JSONObject();
            try {
                jo.put(RT_ID, id);
                jo.put(RT_NAME, name);
                jo.put(RT_DESCRIPTION, description);
                jo.put(RT_RATING, rating);
                jo.put(RT_CREATOR, creator);
                jo.put(RT_DATE, date);
                jo.put(RT_SERVING, serving);
                jo.put(RT_TIME, time);
                jo.put(RT_PHOTO, photo);

                JSONArray ji = new JSONArray();
                for (int i = 0; i < ingredients.length; i++) {
                    ji.put(ingredients[i]);
                }
                jo.put(INGREDIENTS_TABLE, ji);

                JSONArray jd = new JSONArray();
                for (int d = 0; d < directions.length; d++) {
                    JSONObject diro = new JSONObject();
                    diro.put(DT_STEP, directions[d]);
                    diro.put(DT_PHOTO, directions_photos[d]);
                    jd.put(diro);
                }
                jo.put(DIRECTIONS_TABLE, jd);

                JSONArray jt = new JSONArray();
                for (int t = 0; t < tags.length; t++) {
                    jt.put(tags[t]);
                }
                jo.put(TAGS_TABLE, jt);

            } catch (JSONException e) {
                //            e.printStackTrace();
            }
            return jo;
        }

        // Create a Recipe object from a JSON string
        public static Recipe parseJSON(String json) {
            try {
                return Recipe.parseJSON(new JSONObject(json));
            } catch (JSONException e) {
                //            Log.i(TAG, e.toString());
                return null;
            }
        }

        public static Recipe parseJSON(JSONObject jo) {
            Recipe r = new Recipe();
            // Basic fields
            r.id = jo.optLong(RT_ID);
            r.name = jo.optString(RT_NAME);
            r.description = jo.optString(RT_DESCRIPTION);
            r.rating = (float) jo.optDouble(RT_RATING);
            r.creator = jo.optString(RT_CREATOR);
            r.date = jo.optString(RT_DATE);
            r.serving = jo.optInt(RT_SERVING);
            r.time = jo.optInt(RT_TIME);
            r.photo = jo.optString(RT_PHOTO);

            // Remove local links from imported recipes
            // TODO sharing, etc.
            if (!r.photo.startsWith("http")) {
                r.photo = "";
            }

            JSONArray ji = jo.optJSONArray(INGREDIENTS_TABLE);
            JSONArray jd = jo.optJSONArray(DIRECTIONS_TABLE);
            JSONArray jt = jo.optJSONArray(TAGS_TABLE);

            // Ingredients
            if (ji != null) {
                r.ingredients = new String[ji.length()];
                for (int i = 0; i < r.ingredients.length; i++) {
                    r.ingredients[i] = ji.optString(i);
                }
            }

            // Directions (remember that these are ordered!)
            if (jd != null) {
                r.directions = new String[jd.length()];
                r.directions_photos = new String[jd.length()];
                for (int d = 0; d < r.directions.length; d++) {
                    JSONObject diro = jd.optJSONObject(d);
                    if (diro != null) {
                        r.directions[d] = diro.optString(DT_STEP);
                        r.directions_photos[d] = diro.optString(DT_PHOTO);

                        // remove local links
                        // TODO sharing, etc.
                        if (!r.directions_photos[d].startsWith("http")) {
                            r.directions_photos[d] = "";
                        }
                    } else {
                        r.directions[d] = jd.optString(d);
                        r.directions_photos[d] = null;
                    }
                }
            }

            // Tags
            if (jt != null) {
                r.tags = new String[jt.length()];
                for (int t = 0; t < r.tags.length; t++) {
                    r.tags[t] = jt.optString(t);
                }
            }
            return r;
        }
    }

    class DbHelper extends SQLiteOpenHelper {
        private static final String RT_FOREIGN_KEY = " integer references " + RECIPES_TABLE + "(" + RT_ID
                + ") deferrable initially deferred";
        private Context app;

        public DbHelper(Context context) {
            super(context, DB_FILENAME, null, DB_VERSION);
            app = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            //         Log.i(TAG, "Creating database: " + DB_FILENAME);
            synchronized (DB_LOCK) {
                createRecipeTable(db);
                createSecondaryTables(db);
                createCacheTable(db);

                // Import starter recipes
                // TODO Make actual starter recipes, instead of tutorials
                InputStream is = null;
                try {
                    is = app.getResources().openRawResource(R.raw.starter);
                    byte[] buffer = new byte[is.available()];
                    is.read(buffer);
                    JSONArray ja = new JSONArray(new String(buffer));
                    for (int i = 0; i < ja.length(); i++) {
                        Recipe r = Recipe.parseJSON(ja.getJSONObject(i));

                        ContentValues values = createRecipeForInsert(r);
                        long rowid = db.insertWithOnConflict(RECIPES_TABLE, null, values,
                                SQLiteDatabase.CONFLICT_IGNORE);

                        if (r.ingredients != null) {
                            for (String ing : r.ingredients) {
                                ContentValues cvi = createIngredientsCV(rowid, ing);
                                db.insertWithOnConflict(INGREDIENTS_TABLE, null, cvi,
                                        SQLiteDatabase.CONFLICT_IGNORE);
                            }
                        }

                        if (r.directions != null) {
                            int step = 1;
                            for (String dir : r.directions) {
                                ContentValues cdirs = createDirectionsCV(rowid, step, dir,
                                        r.directions_photos[step - 1]);
                                db.insertWithOnConflict(DIRECTIONS_TABLE, null, cdirs,
                                        SQLiteDatabase.CONFLICT_IGNORE);
                                step++;
                            }
                        }

                        if (r.tags != null) {
                            for (String tag : r.tags) {
                                ContentValues ctags = createTagsCV(rowid, tag);
                                db.insertWithOnConflict(TAGS_TABLE, null, ctags, SQLiteDatabase.CONFLICT_IGNORE);
                            }
                        }
                    }
                } catch (IOException e) {
                    //            Log.e(TAG, e.toString());
                } catch (JSONException e) {
                    // TODO handle and try to continue
                    //            Log.e(TAG, e.toString());
                } finally {
                    if (is != null) {
                        try {
                            is.close();
                        } catch (IOException e) {
                            //                  Log.e(TAG, e.toString());
                        }
                    }
                }
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            synchronized (DB_LOCK) {
                if (db.isReadOnly()) {
                    db = SQLiteDatabase.openDatabase(db.getPath(), null, SQLiteDatabase.OPEN_READWRITE);
                    //            Log.i(TAG, "Successfully opened read-write database");
                } else {
                    if (oldVersion <= 8) {
                        createCacheTable(db);
                    }
                    if (oldVersion <= 7) {
                        db.execSQL("alter table " + DIRECTIONS_TABLE + " add column " + DT_PHOTO + " text");
                    } else if (oldVersion < 7) {
                        db.beginTransaction();
                        db.execSQL("drop table " + INGREDIENTS_TABLE);
                        db.execSQL("drop table " + DIRECTIONS_TABLE);
                        db.execSQL("drop table " + TAGS_TABLE);
                        db.execSQL("drop table " + RECIPES_TABLE);
                        this.onCreate(db);
                        db.endTransaction();
                    }
                }
            }

        }

        private void createRecipeTable(SQLiteDatabase db) {
            synchronized (DB_LOCK) {
                db.execSQL("create table " + RECIPES_TABLE + " (" + RT_ID + " integer primary key, " + RT_NAME
                        + " text, " + RT_DESCRIPTION + " text, " + RT_RATING + " real, " + RT_CREATOR + " text, "
                        + RT_DATE + " text, " + RT_SERVING + " integer, " + RT_PHOTO + " text, " + RT_TIME
                        + " integer, " + createUnique(RT_NAME, RT_DESCRIPTION) + ")");
            }
        }

        private void createSecondaryTables(SQLiteDatabase db) {
            synchronized (DB_LOCK) {
                db.execSQL("create table " + INGREDIENTS_TABLE + " (" + IT_ID + " integer primary key, " + IT_NAME
                        + " text, " + IT_RECIPE_ID + RT_FOREIGN_KEY + ")");

                db.execSQL("create table " + DIRECTIONS_TABLE + " (" + DT_ID + " integer primary key, " + DT_STEP
                        + " text, " + DT_SEQUENCE + " int, " + DT_PHOTO + " text, " + DT_RECIPE_ID + RT_FOREIGN_KEY
                        + ")");

                db.execSQL("create table " + TAGS_TABLE + " (" + TT_ID + " integer primary key, " + TT_TAG
                        + " text, " + TT_RECIPE_ID + RT_FOREIGN_KEY + ")");
            }
        }

        private void createCacheTable(SQLiteDatabase db) {
            synchronized (DB_LOCK) {
                db.execSQL("create table " + CACHE_TABLE + " (" + CT_ID + " integer primary key, " + CT_URI
                        + " text, " + CT_CACHED + ")");
            }
        }

        private String createUnique(String... args) {
            return "unique (" + TextUtils.join(",", args) + ")";
        }
    }

    private final DbHelper dbHelper;

    public RecipeData(Context context) {
        dbHelper = new DbHelper(context);
        //      Log.i(TAG, "Initialized database version " + dbHelper.getReadableDatabase().getVersion());
    }

    public void close() {
        dbHelper.close();
    }

    private Cursor queryBuilder(String selection, String[] selectionArgs, String sortBy) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            String outerJoin = createOuterJoin(RECIPES_TABLE, TAGS_TABLE, RT_ID, TT_RECIPE_ID);
            String[] fields = RECIPES_FIELDS;
            fields[0] = RECIPES_TABLE + "." + RT_ID;
            return db.query(outerJoin, fields, selection, selectionArgs, fields[0], null, sortBy);
        }
    }

    public Cursor query(String search, String tag, int min, int max, String sortBy) {
        // this is the "multi-query" method, that searches by name, tag, and time
        //      Log.i(TAG, "multi-query: search = " + search + ", tag = " + tag + ", time = " + min + " to " + max + ", sort by = " + sortBy);
        String like = createLikePattern();
        String time = createTimeComparisonPattern();
        String searchPart = "(" + like + ")";
        String timePart = "(" + time + ")";
        String tagPart = "(" + TT_TAG + " = ?)";
        // We have to match like this because of Android bug 3153
        String match = "%" + search + "%";

        ArrayList<String> parts = new ArrayList<String>();
        ArrayList<String> args = new ArrayList<String>();
        if (search != null) {
            parts.add(searchPart);
            args.add(match);
            args.add(match);
        }
        if (!(min == 0 && max == 0)) {
            parts.add(timePart);
            args.add(Integer.toString(max));
            args.add(Integer.toString(min));
        }
        if (tag != null) {
            parts.add(tagPart);
            args.add(tag);
        }
        String selection = TextUtils.join(" and ", parts);
        String[] selectionArgs = (String[]) args.toArray(new String[args.size()]);
        //      Log.i(TAG, selection + ", " + selectionArgs + ", " + sortBy);
        return queryBuilder(selection, selectionArgs, sortBy);
    }

    private String createInnerJoin(String left, String right, String left_id, String right_id) {
        String table1id = left + "." + left_id;
        String table2id = right + "." + right_id;
        return left + " inner join " + right + " on " + table1id + " = " + table2id;
    }

    private String createOuterJoin(String left, String right, String left_id, String right_id) {
        String table1id = left + "." + left_id;
        String table2id = right + "." + right_id;
        return left + " left outer join " + right + " on " + table1id + " = " + table2id;
    }

    private String createLikePattern() {
        // See above re: query bug #3153
        //      return RT_NAME + " like '%" + strings[0] + "%' or " + RT_DESCRIPTION + " like '%" + strings[1] + "%'";
        return RT_NAME + " like ? or " + RT_DESCRIPTION + " like ?";
    }

    private String createTimeComparisonPattern() {
        return RT_TIME + " <= ? and " + RT_TIME + " >= ?";
    }

    public long getLastInsertRecipeId() {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            Cursor c = db.rawQuery("select max(_id) from " + RECIPES_TABLE, null);
            c.moveToFirst();
            long id = c.getLong(0);
            c.close();
            return id;
        }
    }

    public long getNumberOfRecipes() {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            Cursor c = db.query(RECIPES_TABLE, new String[] { "_id" }, null, null, null, null, null);
            long num = c.getCount();
            c.close();
            return num;
        }
    }

    public Cursor getAllRecipes(String sortBy) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(RECIPES_TABLE, RECIPES_FIELDS, null, null, null, null, sortBy);
        }
    }

    public Cursor getAllRecipes() {
        return getAllRecipes(RT_NAME);
    }

    public Cursor getAllRecipes(String[] columns, String sortBy) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(RECIPES_TABLE, columns, null, null, null, null, sortBy);
        }
    }

    public Cursor getAllRecipes(String[] columns) {
        return getAllRecipes(columns, RT_NAME);
    }

    public Cursor getMatchingRecipes(String match, String sortBy) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            // We have to match like this because of Android bug 3153
            String fixed = "%" + match + "%";
            return db.query(RECIPES_TABLE, RECIPES_FIELDS, createLikePattern(), new String[] { fixed, fixed }, null,
                    null, sortBy);
        }
    }

    public Cursor getMatchingRecipes(String match) {
        return getMatchingRecipes(match, RT_NAME);
    }

    public Cursor getMatchingRecipesByTime(int max, int min, String sortBy) {
        String[] times = { Integer.toString(max), Integer.toString(min) };
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(RECIPES_TABLE, RECIPES_FIELDS, createTimeComparisonPattern(), times, null, null,
                    sortBy);
        }
    }

    public Cursor getMatchingRecipesByTime(int max, int min) {
        return getMatchingRecipesByTime(max, min, RT_NAME);
    }

    public Cursor getSingleRecipe(long rid) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(RECIPES_TABLE, RECIPES_FIELDS, RT_ID + " = ?", new String[] { Long.toString(rid) },
                    null, null, null);
        }
    }

    public Recipe getSingleRecipeObject(long rid) {
        Recipe r = new Recipe();
        Cursor c = getSingleRecipe(rid);
        c.moveToFirst();
        r.id = c.getLong(c.getColumnIndex(RT_ID));
        r.name = c.getString(c.getColumnIndex(RT_NAME));
        r.description = c.getString(c.getColumnIndex(RT_DESCRIPTION));
        r.rating = c.getFloat(c.getColumnIndex(RT_RATING));
        r.creator = c.getString(c.getColumnIndex(RT_CREATOR));
        r.date = c.getString(c.getColumnIndex(RT_DATE));
        r.serving = c.getInt(c.getColumnIndex(RT_SERVING));
        r.time = c.getInt(c.getColumnIndex(RT_TIME));
        r.photo = c.getString(c.getColumnIndex(RT_PHOTO));

        r.ingredients = getRecipeIngredientStrings(rid);
        r.directions = getRecipeDirectionStrings(rid);
        r.directions_photos = getRecipeDirectionPhotoStrings(rid);
        r.tags = getRecipeTagStrings(rid);

        c.close();
        return r;
    }

    public Cursor getRecipeIngredients(long rid) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(INGREDIENTS_TABLE, INGREDIENTS_FIELDS, IT_RECIPE_ID + " = ?",
                    new String[] { Long.toString(rid) }, null, null, null);
        }
    }

    public String[] getRecipeIngredientStrings(long rid) {
        Cursor c = getRecipeIngredients(rid);
        String[] ings = new String[c.getCount()];

        c.moveToFirst();
        while (!c.isAfterLast()) {
            ings[c.getPosition()] = c.getString(c.getColumnIndex(IT_NAME));
            c.moveToNext();
        }

        c.close();
        return ings;
    }

    public Cursor getRecipeDirections(long rid) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(DIRECTIONS_TABLE, DIRECTIONS_FIELDS, DT_RECIPE_ID + " = ?",
                    new String[] { Long.toString(rid) }, null, null, DT_SEQUENCE);
        }
    }

    public String[] getRecipeDirectionStrings(long rid) {
        // NOTE: the returned array is in database sequential order
        Cursor c = getRecipeDirections(rid);
        String[] dirs = new String[c.getCount()];

        c.moveToFirst();
        while (!c.isAfterLast()) {
            dirs[c.getPosition()] = c.getString(c.getColumnIndex(DT_STEP));
            c.moveToNext();
        }

        c.close();
        return dirs;
    }

    private String[] getRecipeDirectionPhotoStrings(long rid) {
        // NOTE: the returned array is in database sequential order
        Cursor c = getRecipeDirections(rid);
        String[] dirs = new String[c.getCount()];

        c.moveToFirst();
        while (!c.isAfterLast()) {
            dirs[c.getPosition()] = c.getString(c.getColumnIndex(DT_PHOTO));
            c.moveToNext();
        }

        c.close();
        return dirs;
    }

    public Cursor getRecipeTags(long rid) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(TAGS_TABLE, TAGS_FIELDS, TT_RECIPE_ID + " = ?", new String[] { Long.toString(rid) },
                    null, null, null);
        }
    }

    public String[] getRecipeTagStrings(long rid) {
        Cursor c = getRecipeTags(rid);
        String[] tags = new String[c.getCount()];

        c.moveToFirst();
        while (!c.isAfterLast()) {
            tags[c.getPosition()] = c.getString(c.getColumnIndex(TT_TAG));
            c.moveToNext();
        }

        c.close();
        return tags;
    }

    public Cursor getRecipesByTag(String tag, String sortBy) {
        String[] fields = RECIPES_FIELDS;
        fields[0] = RECIPES_TABLE + "." + RT_ID;
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(createInnerJoin(RECIPES_TABLE, TAGS_TABLE, RT_ID, TT_RECIPE_ID), fields,
                    TT_TAG + " = ?", new String[] { tag }, null, null, sortBy);
        }
    }

    public Cursor getRecipesByTag(String tag) {
        return getRecipesByTag(tag, RT_NAME);
    }

    public Cursor getAllTags() {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            return db.query(TAGS_TABLE, TAGS_FIELDS, null, null, TT_TAG, null, TT_TAG);
        }
    }

    public boolean recipeHasTag(long rid, String tag) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            Cursor c = db.query(TAGS_TABLE, TAGS_FIELDS, "(" + TT_TAG + " = ?) and (" + TT_RECIPE_ID + " = ?)",
                    new String[] { tag, Long.toString(rid) }, null, null, null);
            boolean hasTag = c.getCount() != 0;
            c.close();
            return hasTag;
        }
    }

    public int insertRecipe(ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            int ret = -1;
            try {
                ret = (int) db.insert(RECIPES_TABLE, null, values);
            } finally {
                db.close();
            }
            return ret;
        }
    }

    public void insertIngredients(ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.insertWithOnConflict(INGREDIENTS_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public void insertDirections(ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.insertWithOnConflict(DIRECTIONS_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public void insertTags(ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.insertWithOnConflict(TAGS_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public int insertRecipe(Recipe r) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            int ret = -1;
            try {
                ContentValues values = createRecipeForInsert(r);
                long rowid = db.insert(RECIPES_TABLE, null, values);

                if (r.ingredients != null) {
                    for (String ing : r.ingredients) {
                        ContentValues cvi = createIngredientsCV(rowid, ing);
                        db.insertWithOnConflict(INGREDIENTS_TABLE, null, cvi, SQLiteDatabase.CONFLICT_IGNORE);
                    }
                }

                if (r.directions != null) {
                    int step = 1;
                    for (String dir : r.directions) {
                        ContentValues cdirs = createDirectionsCV(rowid, step, dir, r.directions_photos[step - 1]);
                        db.insertWithOnConflict(DIRECTIONS_TABLE, null, cdirs, SQLiteDatabase.CONFLICT_IGNORE);
                        step++;
                    }
                }

                if (r.tags != null) {
                    for (String tag : r.tags) {
                        ContentValues ctags = createTagsCV(rowid, tag);
                        db.insertWithOnConflict(TAGS_TABLE, null, ctags, SQLiteDatabase.CONFLICT_IGNORE);
                    }
                }
                ret = (int) rowid;
            } finally {
                db.close();
            }
            return ret;
        }
    }

    public static ContentValues createTagsCV(long rowid, String tag) {
        ContentValues ctags = new ContentValues();
        ctags.put(TT_TAG, tag);
        ctags.put(TT_RECIPE_ID, rowid);
        return ctags;
    }

    public static ContentValues createDirectionsCV(long rowid, int step, String dir, String photo) {
        ContentValues cdirs = new ContentValues();
        cdirs.put(DT_STEP, dir);
        cdirs.put(DT_SEQUENCE, step);
        cdirs.put(DT_PHOTO, photo);
        cdirs.put(DT_RECIPE_ID, rowid);
        return cdirs;
    }

    public static ContentValues createIngredientsCV(long rowid, String ing) {
        ContentValues cvi = new ContentValues();
        cvi.put(IT_NAME, ing);
        cvi.put(IT_RECIPE_ID, rowid);
        return cvi;
    }

    public static ContentValues createRecipeForInsert(Recipe r) {
        ContentValues values = new ContentValues();
        values.put(RT_NAME, r.name);
        values.put(RT_DESCRIPTION, r.description);
        values.put(RT_RATING, r.rating);
        values.put(RT_CREATOR, r.creator);
        values.put(RT_DATE, r.date);
        values.put(RT_SERVING, r.serving);
        values.put(RT_TIME, r.time);
        values.put(RT_PHOTO, r.photo);
        return values;
    }

    public void deleteRecipe(long rid) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.delete(INGREDIENTS_TABLE, IT_RECIPE_ID + " = ?", new String[] { Long.toString(rid) });
                db.delete(DIRECTIONS_TABLE, DT_RECIPE_ID + " = ?", new String[] { Long.toString(rid) });
                db.delete(TAGS_TABLE, TT_RECIPE_ID + " = ?", new String[] { Long.toString(rid) });
                db.delete(RECIPES_TABLE, RT_ID + " = ?", new String[] { Long.toString(rid) });
            } finally {
                db.close();
            }
        }
    }

    public int updateRecipe(Recipe r) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            int ret = -1;
            try {
                long rid = r.id;
                String[] whereArgs = { Long.toString(rid) };
                ret = db.update(RECIPES_TABLE, createRecipeForInsert(r), RT_ID + " = ?",
                        new String[] { Long.toString(r.id) });

                // TODO until we can figure out a smarter way to update
                db.delete(INGREDIENTS_TABLE, IT_RECIPE_ID + " = ?", whereArgs);
                for (String ing : r.ingredients) {
                    db.insertWithOnConflict(INGREDIENTS_TABLE, null, createIngredientsCV(rid, ing),
                            SQLiteDatabase.CONFLICT_IGNORE);
                }

                db.delete(DIRECTIONS_TABLE, DT_RECIPE_ID + " = ?", whereArgs);
                int step = 1;
                for (String dir : r.directions) {
                    db.insertWithOnConflict(DIRECTIONS_TABLE, null,
                            createDirectionsCV(rid, step, dir, r.directions_photos[step - 1]),
                            SQLiteDatabase.CONFLICT_IGNORE);
                    step++;
                }

                db.delete(TAGS_TABLE, TT_RECIPE_ID + " = ?", whereArgs);
                for (String tag : r.tags) {
                    db.insertWithOnConflict(TAGS_TABLE, null, createTagsCV(rid, tag),
                            SQLiteDatabase.CONFLICT_IGNORE);
                }
            } finally {
                db.close();
            }
            return ret;
        }
    }

    public int updateRecipe(long rid, ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            int ret = -1;
            try {
                ret = db.update(RECIPES_TABLE, values, RT_ID + " = ?", new String[] { Long.toString(rid) });
            } finally {
                db.close();
            }
            return ret;
        }
    }

    public void updateIngredients(long rid, ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.updateWithOnConflict(INGREDIENTS_TABLE, values, IT_RECIPE_ID + " = ?",
                        new String[] { Long.toString(rid) }, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public void updateDirections(long rid, ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.updateWithOnConflict(DIRECTIONS_TABLE, values, DT_RECIPE_ID + " = ?",
                        new String[] { Long.toString(rid) }, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public void updateTags(long rid, ContentValues values) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            try {
                db.updateWithOnConflict(TAGS_TABLE, values, TT_RECIPE_ID + " = ?",
                        new String[] { Long.toString(rid) }, SQLiteDatabase.CONFLICT_IGNORE);
            } finally {
                db.close();
            }
        }
    }

    public String findCacheEntry(String uri) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            Cursor c = db.query(CACHE_TABLE, CACHE_FIELDS, CT_URI + " = ?", new String[] { uri }, null, null, null);
            if (c.getCount() > 0) {
                c.moveToFirst();
                String s = c.getString(c.getColumnIndex(CT_CACHED));
                c.close();
                return s;
            } else {
                c.close();
                return null;
            }
        }
    }

    public boolean isCached(String uri) {
        return findCacheEntry(uri) != null;
    }

    public String getOldestCacheEntry() {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getReadableDatabase();
            Cursor c = db.query(CACHE_TABLE, CACHE_FIELDS, null, null, null, null, CT_CACHED, "1");
            if (c.getCount() > 0) {
                c.moveToFirst();
                String s = c.getString(c.getColumnIndex(CT_URI));
                c.close();
                return s;
            } else {
                c.close();
                return null;
            }
        }
    }

    public void insertCacheEntry(String uri, String cached) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(CT_URI, uri);
            values.put(CT_CACHED, cached);

            if (isCached(uri)) {
                db.update(CACHE_TABLE, values, CT_URI + " = ?", new String[] { cached });
            } else {
                db.insert(CACHE_TABLE, null, values);
            }
        }
    }

    public void removeCacheEntry(String uri) {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();

            if (isCached(uri)) {
                db.delete(CACHE_TABLE, CT_URI + "= ?", new String[] { uri });
            }
        }
    }

    public String removeOldestCacheEntry() {
        // returns the filename of the (now-deleted) oldest cache entry
        String uri = getOldestCacheEntry();
        String filename = findCacheEntry(uri);
        removeCacheEntry(uri);
        return filename;
    }

    public void clearCache() {
        synchronized (DB_LOCK) {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            db.delete(CACHE_TABLE, null, null);
        }
    }

    // TODO rewrite the import/export to allow user choices
    public ArrayList<Recipe> importRecipes(String path) throws IOException {
        File f = new File(URI.create(path));
        FileInputStream fis = new FileInputStream(f);
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        String st = new String(buffer);
        return parseJsonRecipes(st);
    }

    public ArrayList<Recipe> parseJsonRecipes(String st) {
        ArrayList<Recipe> recipes = new ArrayList<Recipe>();
        try {
            JSONArray ja = new JSONArray(st);
            for (int i = 0; i < ja.length(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                Recipe r = Recipe.parseJSON(jo);
                if (r != null) {
                    recipes.add(r);
                }
            }
        } catch (JSONException e) {
            //         Log.e(TAG, e.toString());
            // TODO handle exception
        }
        return recipes;
    }

    public void insertImportedRecipes(ArrayList<Recipe> recipes) {
        for (Recipe r : recipes) {
            if (r != null) {
                insertRecipe(r);
            }
        }
    }

    public String exportRecipes(long[] ids) throws IOException {
        // TODO file selection, sharing, etc.
        String filename = "exported-recipes-" + System.currentTimeMillis() + ".rcp";
        File sd = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);
        File export = new File(sd, filename);
        FileOutputStream fos = null;
        sd.mkdirs();
        try {
            fos = new FileOutputStream(export);
            JSONArray ja = new JSONArray();
            for (long i : ids) {
                ja.put(getSingleRecipeObject(i).toJSON());
            }
            byte[] buffer = ja.toString().getBytes();
            fos.write(buffer);
            return export.toString();
        } catch (FileNotFoundException e) {
            //         Log.e(TAG, e.toString());
            return null;
        } finally {
            if (fos != null) {
                fos.close();
            }
        }
    }
}