· 9 years ago · Oct 12, 2016, 11:02 AM
1package han.robaben.nl.presentation;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.util.Log;
9
10import java.util.ArrayList;
11import java.util.List;
12
13import han.robaben.nl.presentation.Model.FestivalEvent;
14
15/**
16 * Created by rob on 3-10-16.
17 */
18public class FestivalEventDbHelper extends SQLiteOpenHelper {
19 private static final String TEXT_TYPE = " TEXT";
20 private static final String COMMA_SEP = ",";
21 private static final String SQL_CREATE_ENTRIES =
22 "CREATE TABLE " + FestivalEventContract.FeedEvent.TABLE_NAME + " (" +
23 FestivalEventContract.FeedEvent._ID + " INTEGER PRIMARY KEY," +
24 FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE + TEXT_TYPE + "UNIQUE" + COMMA_SEP +
25 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION + TEXT_TYPE + COMMA_SEP +
26 FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE + TEXT_TYPE + COMMA_SEP +
27 FestivalEventContract.FeedEvent.COLUMN_NAME_WEBSITE + TEXT_TYPE + COMMA_SEP +
28 FestivalEventContract.FeedEvent.COLUMN_NAME_AGE_CATEGORY + TEXT_TYPE + COMMA_SEP +
29 FestivalEventContract.FeedEvent.COLUMN_NAME_THUMB_URL + TEXT_TYPE + COMMA_SEP +
30 FestivalEventContract.FeedEvent.COLUMN_NAME_IMAGES + TEXT_TYPE + COMMA_SEP +
31 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION_TEASER + TEXT_TYPE
32 + " )";
33
34 private static final String SQL_DELETE_ENTRIES =
35 "DROP TABLE IF EXISTS " + FestivalEventContract.FeedEvent.TABLE_NAME;
36
37 // If you change the database schema, you must increment the database version.
38 public static final int DATABASE_VERSION = 7;
39 public static final String DATABASE_NAME = "FeedEvent.db";
40
41 public FestivalEventDbHelper(Context context) {
42 super(context, DATABASE_NAME, null, DATABASE_VERSION);
43 }
44
45 public void onCreate(SQLiteDatabase db) {
46 db.execSQL(SQL_CREATE_ENTRIES);
47 }
48
49 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
50 // This database is only a cache for online data, so its upgrade policy is
51 // to simply to discard the data and start over
52 db.execSQL(SQL_DELETE_ENTRIES);
53 onCreate(db);
54 }
55
56 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
57 onUpgrade(db, oldVersion, newVersion);
58 }
59
60 public void deleteAll()
61 {
62 SQLiteDatabase db= this.getWritableDatabase();
63 db.delete(FestivalEventContract.FeedEvent.TABLE_NAME, null, null);
64 }
65
66 public void addEventsToDatabase(FestivalEvent[] retrievedEvents, SQLiteDatabase db){
67 ContentValues values = new ContentValues();
68
69 // Your database schema
70 String[] mProjection = {
71 FestivalEventContract.FeedEvent._ID,
72 FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE,
73 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION,
74 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION_TEASER,
75 FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE,
76 FestivalEventContract.FeedEvent.COLUMN_NAME_WEBSITE,
77 FestivalEventContract.FeedEvent.COLUMN_NAME_AGE_CATEGORY,
78 FestivalEventContract.FeedEvent.COLUMN_NAME_THUMB_URL,
79 FestivalEventContract.FeedEvent.COLUMN_NAME_IMAGES,
80 };
81
82
83 String selection = FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE + " = ?";
84 for (FestivalEvent event : retrievedEvents) {
85
86 // Here we query database
87 Cursor doesExistCursor = db.query(
88 FestivalEventContract.FeedEvent.TABLE_NAME,
89 mProjection,
90 null,
91 null,
92 null,
93 null,
94 null);
95
96 // Add all data we need
97 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE, event.title);
98 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_THUMB_URL, event.getThumbUrl());
99 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_IMAGES, event.getLargeImageUrl());
100 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION, event.description);
101 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION_TEASER, event.descriptionTeaser);
102 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_AGE_CATEGORY, event.ageCategory);
103 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE, event.genre);
104 values.put(FestivalEventContract.FeedEvent.COLUMN_NAME_WEBSITE, event.website);
105
106 // Only add new one if it does not exist
107 if (doesExistCursor == null || !doesExistCursor.moveToFirst()) {
108 // Create a new map of values, where column names are the keys
109 db.insert(FestivalEventContract.FeedEvent.TABLE_NAME, null, values);
110 } else {
111 // Update the current value
112 db.replace(FestivalEventContract.FeedEvent.TABLE_NAME, null, values);
113 }
114 }
115 }
116
117 public List<String> getListOfGenres(SQLiteDatabase db) {
118 List<String> allGenres = new ArrayList<>();
119
120 // How you want the results sorted in the resulting Cursor
121 final String sortOrder =
122 FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE + " ASC";
123
124 // Your database schema
125 String[] mProjection = {
126 FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE
127 };
128
129 final String selection = FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE + " = ?";
130
131 Cursor c = db.query(
132 true,
133 FestivalEventContract.FeedEvent.TABLE_NAME, // The table to query
134 mProjection, // The columns to return
135 null, // The columns for the WHERE clause
136 null, // The values for the WHERE clause
137 null, // don't group the rows
138 null, // don't filter by row groups
139 sortOrder, // The sort order
140 null // No limit
141
142 );
143
144 c.moveToFirst();
145
146 Log.e("Test" , String.valueOf(c.getCount()));
147
148 if(c.getCount() > 0){
149 do {
150 allGenres.add(c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE)));
151 c.move(1);
152 } while (!c.isLast());
153 }
154
155 return allGenres;
156 }
157
158 //TODO: This does not seem to be optimal
159 public FestivalEvent getEventById(SQLiteDatabase db, int id, Context context) {
160 FestivalEvent event = new FestivalEvent();
161
162 // How you want the results sorted in the resulting Cursor
163 final String sortOrder =
164 FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE + " DESC";
165
166 // Your database schema
167 String[] mProjection = {
168 FestivalEventContract.FeedEvent._ID,
169 FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE,
170 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION,
171 FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION_TEASER,
172 FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE,
173 FestivalEventContract.FeedEvent.COLUMN_NAME_WEBSITE,
174 FestivalEventContract.FeedEvent.COLUMN_NAME_AGE_CATEGORY,
175 FestivalEventContract.FeedEvent.COLUMN_NAME_IMAGES,
176 FestivalEventContract.FeedEvent.COLUMN_NAME_THUMB_URL
177 };
178
179 String[] selectionArgs = {String.valueOf(id)};
180
181 final String selection = FestivalEventContract.FeedEvent._ID + " = ?";
182
183 Cursor c = db.query(
184 FestivalEventContract.FeedEvent.TABLE_NAME, // The table to query
185 mProjection, // The columns to return
186 selection, // The columns for the WHERE clause
187 selectionArgs, // The values for the WHERE clause
188 null, // don't group the rows
189 null, // don't filter by row groups
190 sortOrder // The sort order
191 );
192
193 c.moveToFirst();
194
195 if (c.getCount() > 0) {
196 event.title = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_TITLE));
197 event.descriptionTeaser = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION_TEASER));
198 event.description = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_DESCRIPTION));
199 event.ageCategory = stripAge(c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE)));
200 event.genre = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_GENRE));
201 event.website = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_WEBSITE));
202 event.largeImageUrl = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_IMAGES));
203 event.thumbUrl = c.getString(c.getColumnIndex(FestivalEventContract.FeedEvent.COLUMN_NAME_THUMB_URL));
204 }
205 return event;
206 }
207
208 private String stripAge(String age){
209 if(age.contains("Age") && !age.contains("All")){
210 return age.replace("Age", "");
211 };
212 return age;
213 }
214
215}