Android Open Source - volumescheduler Database Helper

From Project

License

The source code is released under:
GNU General Public License
If you think the Android project volumescheduler listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
Java Source Code

/*
 * Copyright (c) 2014 RuneCasters IT Solutions.
 *//w  w w. j a v a 2  s . co m
 * This file is part of VolumeScheduler.
 *
 * VolumeScheduler is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * VolumeScheduler is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with VolumeScheduler.  If not, see <http://www.gnu.org/licenses/>.
 */

package au.com.runecasters.volumescheduler.model;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static int sRuleCount;
    private static final int DB_VERSION = 5;
    private static DatabaseHelper sInstance;

    private DatabaseHelper(Context context) {
        super(context, "VolumeSchedulerRules", null, DB_VERSION);
    }

    public static DatabaseHelper getInstance(Context context) {
        if (sInstance == null) {
            sInstance = new DatabaseHelper(context);
        }
        return sInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        setup(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        setup(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }

    private void setup(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS RuleList");
        db.execSQL("CREATE TABLE RuleList " +
                "(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "ruleType INTEGER, " +
                "ruleName TEXT, " +
                "priority INTEGER, " +
                "volRingtone INTEGER, " +
                "volNotification INTEGER, " +
                "volMedia INTEGER, " +
                "volAlarm INTEGER, " +
                "vibrate INTEGER)");
        db.execSQL("DROP TRIGGER IF EXISTS AdjustPrioritiesAfterDeletion");
        db.execSQL("CREATE TRIGGER AdjustPrioritiesAfterDeletion " +
                "AFTER DELETE ON RuleList " +
                "BEGIN " +
                    "UPDATE RuleList " +
                    "SET priority = priority - 1 " +
                    "WHERE priority > old.priority; " +
                "END");
        db.execSQL("DROP TABLE IF EXISTS CalendarRules");
        db.execSQL("CREATE TABLE CalendarRules " +
                "(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "ruleID INTEGER UNIQUE REFERENCES RuleList (id) ON DELETE CASCADE ON UPDATE CASCADE, " +
                "calendarID INTEGER, " +
                "searchTitle TEXT, " +
                "searchLocation TEXT, " +
                "searchDesc TEXT, " +
                "availability INTEGER)");
        db.execSQL("DROP TABLE IF EXISTS TimeDayRules");
        db.execSQL("CREATE TABLE TimeDayRules " +
                "(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "ruleID INTEGER UNIQUE REFERENCES RuleList (id) ON DELETE CASCADE ON UPDATE CASCADE, " +
                "startHour INTEGER, " +
                "startMinute INTEGER, " +
                "endHour INTEGER, " +
                "endMinute INTEGER, " +
                "daysOfWeek TEXT)");
    }

    public ArrayList<SchedulerRule> getRules() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor ruleCursor = db.query("RuleList", null, null, null, null, null, "4 ASC", null);
        ArrayList<SchedulerRule> ruleList = new ArrayList<>(ruleCursor.getCount());
        sRuleCount = ruleCursor.getCount();
        while (ruleCursor.moveToNext()) {
            SchedulerRule newRule = new SchedulerRule(ruleCursor.getInt(1), ruleCursor.getLong(0));
            newRule.setRuleName(ruleCursor.getString(2));
            // Set rule volumes
            if (ruleCursor.getInt(4) >= 0) {
                newRule.changeRingtone(true, ruleCursor.getInt(4));
            }
            if (ruleCursor.getInt(5) >= 0) {
                newRule.changeNotification(true, ruleCursor.getInt(5));
            }
            if (ruleCursor.getInt(6) >= 0) {
                newRule.changeMedia(true, ruleCursor.getInt(6));
            }
            if (ruleCursor.getInt(7) >= 0) {
                newRule.changeAlarm(true, ruleCursor.getInt(7));
            }
            newRule.setVibrate(ruleCursor.getInt(8) != 0);
            if (newRule.getRuleType() == SchedulerRule.CALENDAR_RULE) {
                // Set rule calendar trigger
                Cursor calCursor = db.query("CalendarRules", null, "ruleID = " + ruleCursor.getInt(0),
                        null, null, null, null, null);
                calCursor.moveToFirst();
                newRule.setCalendarID(calCursor.getLong(2));
                newRule.setSearchTitle(calCursor.getString(3));
                newRule.setSearchLocation(calCursor.getString(4));
                newRule.setSearchDesc(calCursor.getString(5));
                newRule.setAvailability(calCursor.getInt(6));
                calCursor.close();
            } else if (newRule.getRuleType() == SchedulerRule.TIME_RULE) {
                Cursor timeCursor = db.query("TimeDayRules", null, "ruleID = " + ruleCursor.getInt(0),
                        null, null, null, null, null);
                timeCursor.moveToFirst();
                newRule.setStartTime(new int[] {timeCursor.getInt(2), timeCursor.getInt(3)});
                newRule.setEndTime(new int[] {timeCursor.getInt(4), timeCursor.getInt(5)});
                newRule.setDaysOfWeekFromSerial(timeCursor.getString(6));
                timeCursor.close();
            }
            ruleList.add(newRule);
        }
        ruleCursor.close();
        db.close();
        return ruleList;
    }

    public void addRule(SchedulerRule newRule) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues ruleListVals = newRule.getBaseRuleValues();
        ruleListVals.put("priority", ++sRuleCount);
        long newRuleID = db.insert("RuleList", null, ruleListVals);

        if (newRule.getRuleType() == SchedulerRule.CALENDAR_RULE) {
            ContentValues calRuleListVals = newRule.getCalendarRuleValues();
            calRuleListVals.put("ruleID", newRuleID);
            db.insert("CalendarRules", null, calRuleListVals);
        } else if (newRule.getRuleType() == SchedulerRule.TIME_RULE) {
            ContentValues timeRuleListVals = newRule.getTimeDayRuleValues();
            timeRuleListVals.put("ruleID", newRuleID);
            db.insert("TimeDayRules", null, timeRuleListVals);
        }
        db.close();
    }

    public void updateRule(SchedulerRule editedRule) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues ruleListVals = editedRule.getBaseRuleValues();
        db.update("RuleList", ruleListVals, "id = " + editedRule.getRuleID(), null);

        if (editedRule.getRuleType() == SchedulerRule.CALENDAR_RULE) {
            ContentValues calRuleListVals = editedRule.getCalendarRuleValues();
            db.update("CalendarRules", calRuleListVals, "ruleID = " + editedRule.getRuleID(), null);
        } else if (editedRule.getRuleType() == SchedulerRule.TIME_RULE) {
            ContentValues timeRuleListVals = editedRule.getTimeDayRuleValues();
            db.update("TimeDayRules", timeRuleListVals, "ruleID = " + editedRule.getRuleID(), null);
        }
    }

    public void delRule(long ruleID) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete("RuleList", "id = " + ruleID, null);
        db.delete("CalendarRules", "ruleID = " + ruleID, null);
        db.delete("TimeDayRules", "ruleID = " + ruleID, null);
        db.close();
        sRuleCount--;
    }

    public void changeRulePriority(long ruleID, int priorityChange) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor currentPriorityCursor = db.query("RuleList", new String[] {"priority"}, "id = " + ruleID, null, null, null, null, null);
        currentPriorityCursor.moveToFirst();
        int currentPriority = currentPriorityCursor.getInt(0);
        currentPriorityCursor.close();
        if (priorityChange >= 0) {
            db.execSQL("UPDATE RuleList " +
                    "SET priority = priority - 1 " +
                    "WHERE priority BETWEEN " + currentPriority + " AND " + (currentPriority + priorityChange));
        } else {
            db.execSQL("UPDATE RuleList " +
                    "SET priority = priority + 1 " +
                    "WHERE priority BETWEEN " + (currentPriority + priorityChange) + " AND " + currentPriority);
        }
        db.execSQL("UPDATE RuleList " +
                "SET priority = " + (currentPriority + priorityChange) + " " +
                "WHERE id = " + ruleID);
        db.close();
    }
}
Java Source Code List
