BP7jjHnX

1package org.ssdham.divine;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8
9import java.util.ArrayList;
10import java.util.List;
11
12
13/**
14 * Created by sawin on 2/9/18.
15 */
16
17public class DBOpenHelper extends SQLiteOpenHelper {
18    /**
19     * Quote DB TABLE CONST
20     */
21    public static final String TABLE_NAME = "quotes";
22    public static final String QUOTE_ID = "quoteId";
23    public static final String QUOTE = "quote";
24    public static final String AUTHOR = "author";
25    public static final String YEAR = "year";
26    public static final String IS_VIEWED = "isViewed";
27    public static final String VIEWED_DATE = "viewedDate";
28    /**
29     * Database info
30     */
31    private static final String DATABASE_NAME = "quote.db";
32    private static final int DATABASE_VERSION = 2;
33    public static String[] allColumns = {QUOTE_ID, QUOTE, AUTHOR, YEAR, IS_VIEWED, VIEWED_DATE};
34
35    SQLiteDatabase db;
36    ContentValues contentValues;
37
38
39    public DBOpenHelper(Context context) {
40        super(context, DATABASE_NAME, null, DATABASE_VERSION);
41    }
42
43    @Override
44    public void onCreate(SQLiteDatabase sqLiteDatabase) {
45        String SQL_CREATE_QUOTE_TABLE = "CREATE TABLE "
46                + TABLE_NAME + " ("
47                + QUOTE_ID + " INTEGER PRIMARY KEY, "
48                + QUOTE + " TEXT UNIQUE, "
49                + AUTHOR + " TEXT, "
50                + YEAR + " INTEGER, "
51                + IS_VIEWED + " INTEGER DEFAULT 0, "
52                + VIEWED_DATE + " TIMESTAMP NULL "
53                + ");";
54
55        sqLiteDatabase.execSQL(SQL_CREATE_QUOTE_TABLE);
56    }
57
58    @Override
59    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
60        //sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
61        // for database 2 we are not doing anything since there is only change on column for unique quote
62        //onCreate(sqLiteDatabase);
63    }
64
65    public void addQuote(int quoteId, String quote, String author, int year) {
66        db = this.getWritableDatabase();
67        contentValues = new ContentValues();
68        contentValues.put(QUOTE_ID, quoteId);
69        contentValues.put(QUOTE, quote);
70        contentValues.put(AUTHOR, author);
71        contentValues.put(YEAR, year);
72        db.insert(TABLE_NAME, null, contentValues);
73        contentValues.clear();
74        db.close();
75    }
76
77    public void addQuoteIgnoreDuplicate(int quoteId, String quote, String author, int year) {
78        db = this.getWritableDatabase();
79        contentValues = new ContentValues();
80        contentValues.put(QUOTE_ID, quoteId);
81        contentValues.put(QUOTE, quote);
82        contentValues.put(AUTHOR, author);
83        contentValues.put(YEAR, year);
84        db.insertWithOnConflict(TABLE_NAME, null, contentValues, SQLiteDatabase.CONFLICT_IGNORE);
85        contentValues.clear();
86        db.close();
87    }
88
89
90    public Quote getRandomQuote() {
91        Quote quote = null;
92        db = this.getReadableDatabase();
93        Cursor cursor = db.query(TABLE_NAME, allColumns, IS_VIEWED + " != ?",
94                new String[]{"1"}, null, null, " RANDOM()", "1");
95        if (cursor.moveToFirst()) {
96            quote = new Quote();
97            quote.setQuoteId(cursor.getInt(0));
98            quote.setQuote(cursor.getString(1));
99            quote.setAuthor(cursor.getString(2));
100            quote.setYear(cursor.getInt(3));
101        }
102        db.close();
103        return quote;
104    }
105
106    public List<Quote> getAllQuote() {
107        List<Quote> quoteList = new ArrayList<Quote>();
108        db = this.getReadableDatabase();
109        Cursor cursor = db.query(TABLE_NAME, allColumns, null, null,
110                null, null, null);
111        if (cursor.moveToFirst()) {
112            do {
113                Quote quote = new Quote();
114                quote.setQuoteId(cursor.getInt(0));
115                quote.setQuote(cursor.getString(1));
116                quote.setAuthor(cursor.getString(2));
117                quote.setYear(cursor.getInt(3));
118                quote.setViewedDate(cursor.getString(5));
119                quoteList.add(quote);
120            } while (cursor.moveToNext());
121        }
122        return quoteList;
123    }
124
125    public void setViewed(int quoteId) {
126        contentValues = new ContentValues();
127        contentValues.put(IS_VIEWED, 1);
128        db = this.getWritableDatabase();
129        db.update(TABLE_NAME, contentValues, QUOTE_ID + " = " + quoteId, null);
130        contentValues.clear();
131        db.close();
132    }
133
134    public void setViewedDate(int quoteId, String date) {
135        contentValues = new ContentValues();
136        contentValues.put(VIEWED_DATE, date);
137        db = this.getWritableDatabase();
138        db.update(TABLE_NAME, contentValues, QUOTE_ID + " = " + quoteId, null);
139        contentValues.clear();
140        db.close();
141    }
142
143    public Quote getViewedDateQuote(String date) {
144        db = this.getReadableDatabase();
145        Quote quote = null;
146        Cursor cursor = db.query(TABLE_NAME, allColumns, " strftime('%Y-%m-%d',"
147                        + VIEWED_DATE + ") = ? ",
148                new String[]{date}, null, null, null);
149        if (cursor.moveToFirst()) {
150            quote = new Quote();
151            quote.setQuoteId(cursor.getInt(0));
152            quote.setQuote(cursor.getString(1));
153            quote.setAuthor(cursor.getString(2));
154            quote.setYear(cursor.getInt(3));
155            quote.setViewedDate(cursor.getString(5));
156        }
157        db.close();
158        return quote;
159    }
160}