H1KAtTyc

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