Database Operations

new_gist_file.java

package com.cuneytayyildiz.aktuelurunler.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.cuneytayyildiz.aktuelurunler.models.menu.Category;
import com.cuneytayyildiz.aktuelurunler.models.menu.Store;
import com.cuneytayyildiz.aktuelurunler.models.other.ReminderItem;
import com.cuneytayyildiz.usefulthings.utils.CLog;
import com.cuneytayyildiz.usefulthings.utils.DatabaseUtil;

import java.io.File;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class DBAdapter {
    //<editor-fold desc="Variables">
    private static final String DB_NAME = "db.sqlite";
    private static final int DB_VERSION = 6;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    private Context context;
    //</editor-fold>

    public DBAdapter(Context context) {
        this.context = context;
        DBHelper = new DatabaseHelper(context);
    }

    public DBAdapter open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (DBHelper != null) DBHelper.close();
    }

    //<editor-fold desc="Categories">
    public List<Category.CategoriesEntity> getCategories() {
        List<Category.CategoriesEntity> categoryItems = null;
        try {
            open();
            categoryItems = new ArrayList<>();
            Cursor cursor = db.rawQuery("SELECT * FROM categories WHERE visible = 1", null);
            if (cursor != null && !cursor.isClosed()) {
                while (cursor.moveToNext()) {
                    categoryItems.add(new Category.CategoriesEntity(cursor));
                }
            }
            if (cursor != null) {
                cursor.close();
            }
            close();
        } catch (SQLException e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        }finally {
            close();
        }
        return categoryItems;
    }

    public boolean insertCategories(List<Category.CategoriesEntity> categoriesEntities) {
        open();
        db.execSQL("DROP TABLE IF EXISTS [categories];");
        db.execSQL("CREATE TABLE [categories] ([id] INTEGER, [url] TEXT, [title_tr] TEXT, [title_en] TEXT, [visible] BOOL);");
        int counter = 0;
        for (Category.CategoriesEntity categoriesEntity : categoriesEntities) {
            ContentValues values = new ContentValues();
            values.put("id", categoriesEntity.getId());
            values.put("url", categoriesEntity.getUrl());
            values.put("title_tr", categoriesEntity.getTitle_tr());
            values.put("title_en", categoriesEntity.getTitle_en());
            values.put("visible", categoriesEntity.isVisible());
            if (db.insert("categories", null, values) > 0) ++counter;
        }
        boolean result = counter == categoriesEntities.size();
        if (result) {
            changeLastUpdateValue(false); //Check for categories
        }
        close();
        return result;
    }
    //</editor-fold>

    //<editor-fold desc="Stores">
    public List<Store.StoresEntity> getStores() {
        List<Store.StoresEntity> menuItems = null;
        try {
            open();
            menuItems = new ArrayList<>();
            Cursor cursor = db.rawQuery("SELECT * FROM stores WHERE visible = 1", null);
            if (cursor != null && !cursor.isClosed()) {
                while (cursor.moveToNext()) {
                    menuItems.add(new Store.StoresEntity(cursor));
                }
            }
            if (cursor != null) cursor.close();
            close();
        } catch (SQLException e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        } finally {
            close();
        }

        return menuItems;
    }

    public Store.StoresEntity getStoreById(int id) {
        open();
        Store.StoresEntity storeEntity = null;
        Cursor cursor = db.rawQuery("SELECT * FROM stores WHERE id = " + id, null);
        if (cursor != null && !cursor.isClosed()) {
            while (cursor.moveToNext()) {
                storeEntity = new Store.StoresEntity(cursor);
            }
        }
        if (cursor != null) {
            cursor.close();
        }
        close();
        return storeEntity;
    }

    public Store.StoresEntity getStoreByUrl(String url) {
        open();
        Store.StoresEntity menuItem = null;
        Cursor cursor = db.rawQuery(String.format("SELECT * FROM stores where url = '%s'", url.toLowerCase()), null);
        if (cursor != null && !cursor.isClosed()) {
            while (cursor.moveToNext()) {
                menuItem = new Store.StoresEntity(cursor);
            }
        }
        if (cursor != null) {
            cursor.close();
        }
        close();
        return menuItem;
    }

    public List<Store.StoresEntity> geStoresByCategoryId(int categoryId, String sortingType) {
        List<Store.StoresEntity> storeEntities = new ArrayList<>();
        try {
            open();
            Cursor cursor = db.rawQuery("SELECT * FROM stores WHERE visible = 1 AND category = " + categoryId +
                    (!TextUtils.isEmpty(sortingType) ? " ORDER BY title " + sortingType : ""), null);
            if (cursor != null && !cursor.isClosed()) {
                while (cursor.moveToNext()) {
                    storeEntities.add(new Store.StoresEntity(cursor));
                }
            }
            if (cursor != null) {
                cursor.close();
            }
            close();
        } catch (Exception e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        } finally {
            close();
        }
        return storeEntities;
    }

    public boolean insertStores(List<Store.StoresEntity> categoryStoreEntity) {
        open();
        db.execSQL("DROP TABLE IF EXISTS [stores];");
        db.execSQL("CREATE TABLE [stores] ([id] INTEGER, [category] INTEGER, [url] TEXT, [title] TEXT, [visible] BOOL);");
        int counter = 0;
        for (Store.StoresEntity storeEntity : categoryStoreEntity) {
            ContentValues values = new ContentValues();
            values.put("id", storeEntity.getId());
            values.put("category", storeEntity.getCategory());
            values.put("url", storeEntity.getUrl());
            values.put("title", storeEntity.getTitle());
            values.put("visible", storeEntity.isVisible());
            if (db.insert("stores", null, values) > 0) ++counter;
        }
        boolean result = counter == categoryStoreEntity.size();
        if (result) {
            changeLastUpdateValue(true);
        }
        close();
        return result;
    }
    //</editor-fold>

    //<editor-fold desc="Favorites">
    public List<Store.StoresEntity> getFavorites() {
        List<Store.StoresEntity> favoriteEntities = new ArrayList<>();
        try {
            open();
            Cursor cursor = db.rawQuery("SELECT * FROM favorites WHERE visible = 1", null);
            if (cursor != null && !cursor.isClosed()) {
                while (cursor.moveToNext()) {
                    favoriteEntities.add(new Store.StoresEntity(cursor));
                }
            }
            if (cursor != null) {
                cursor.close();
            }
            close();
        } catch (Exception e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        }finally {
            close();
        }
        return favoriteEntities;
    }

    public boolean updateFavorite(Store.StoresEntity favoriteStore, boolean favorite) {
        open();
        boolean result;
        ContentValues values = new ContentValues();
        values.put("id", favoriteStore.getId());
        values.put("category", favoriteStore.getCategory());
        values.put("url", favoriteStore.getUrl());
        values.put("title", favoriteStore.getTitle());
        values.put("visible", favorite);
        int id = (int) db.insertWithOnConflict("favorites", null, values, SQLiteDatabase.CONFLICT_IGNORE);
        result = id != -1 || db.update("favorites", values, String.format("url = '%s'", favoriteStore.getUrl()), null) > 0;
        close();
        return result;
    }

    public boolean isFavoriteExists(String url) {
        int count = 0;
        try {
            open();
            Cursor cursor = db.rawQuery(String.format("SELECT * FROM favorites WHERE url = '%s' and visible = 1", url), null);
            count = cursor.getCount();
            if (!cursor.isClosed()) {
                cursor.close();
            }
            close();
        } catch (SQLiteException e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        }finally {
            close();
        }
        return count > 0;
    }
    //</editor-fold>

    //<editor-fold desc="Reminders">
    public List<ReminderItem> getReminders() {
        open();
        List<ReminderItem> reminderItems = null;
        try {
            reminderItems = new ArrayList<>();
            Cursor cursor = db.rawQuery("SELECT * FROM reminders", null);
            if (cursor != null && !cursor.isClosed()) {
                while (cursor.moveToNext()) {
                    ReminderItem reminderItem = new ReminderItem();
                    reminderItem.setId(cursor.getInt(cursor.getColumnIndex("id")));
                    reminderItem.setMarketId(cursor.getInt(cursor.getColumnIndex("marketId")));
                    reminderItem.setDescription(cursor.getString(cursor.getColumnIndex("description")));
                    reminderItem.setDate(cursor.getString(cursor.getColumnIndex("date")));
                    reminderItem.setMilliseconds(cursor.getLong(cursor.getColumnIndex("milliseconds")));
                    reminderItems.add(reminderItem);
                }
            }
            if (cursor != null) cursor.close();
            close();
        } catch (SQLException e) {
            e.printStackTrace();
            reCreateDbIfError(e);
        }finally {
            close();
        }
        return reminderItems;
    }

    public boolean insertToReminders(ReminderItem marketItem) {
        open();
        ContentValues values = new ContentValues();
        values.put("marketId", marketItem.getMarketId());
        values.put("description", marketItem.getDescription());
        values.put("date", marketItem.getDate());
        values.put("milliseconds", marketItem.getMilliseconds());
        boolean result = db.insert("reminders", null, values) > 0;
        close();
        Log.d("(insertToReminders)", "> called with: " + "marketItem = [" + marketItem + "]" + " result = [" + result + "]");
        return result;
    }

    public boolean deleteFromReminders(long milliseconds) {
        open();
        boolean result = db.delete("reminders", "milliseconds = " + milliseconds, null) > 0;
        close();
        return result;
    }
    //</editor-fold>


    public boolean changeLastUpdateValue(boolean isStore) {
        Calendar now = Calendar.getInstance();
        now.add(Calendar.DATE, isStore ? 3 : 7);

        open();
        ContentValues values = new ContentValues();
        values.put(isStore ? "last_store_update" : "last_category_update", now.getTimeInMillis());
        boolean result = db.update("updates", values, "id = 1", null) > 0;
        close();
        return result;
    }

   

    public static class DatabaseHelper extends SQLiteOpenHelper {
        private Context context;

        DatabaseHelper(Context ctx) {
            super(ctx, DB_NAME, null, DB_VERSION);
            context = ctx;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // if (db.needUpgrade(newVersion)) {
            CLog.d("onUpgrade");
            DatabaseUtil.onUpgrade(context, db, oldVersion, newVersion);
            //   }
        }

        @Override
        public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }

    }
}