· 8 years ago · Jan 24, 2018, 01:44 AM
1package com.domandtom.antiaging;
2
3import java.util.ArrayList;
4import java.util.List;
5
6import android.content.ContentValues;
7import android.content.Context;
8import android.content.UriMatcher;
9import android.database.Cursor;
10import android.database.SQLException;
11import android.database.sqlite.SQLiteDatabase;
12import android.database.sqlite.SQLiteOpenHelper;
13import android.net.Uri;
14import android.util.Log;
15
16
17public class AntiAgingItemsProvider {
18
19 public static final String PROVIDER_NAME = "com.domandtom.provider.AntiAgingItemsProvider";
20 public static final Uri CONTENT_URI = Uri.parse("content://com.domandtom.provider.AntiAgingItemsProvider/anti_aging_items");
21
22 // TODO: Complete
23 public static final String HAIR = "hair";
24 public static final String SKIN = "skin";
25
26 private static final String[] INGREDIENT_STAR = { "_id", "_name", "about", "category" };
27 private static final String PRODUCTS_TABLE = "products";
28 private static final String INGREDIENTS_TABLE = "ingredients";
29
30 public static final int ANTI_AGING_ITEMS = 1;
31 private static final int PRODUCT_ITEM_ID = 2;
32
33 private static final UriMatcher uriMatcher;
34 static
35 {
36 uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
37 uriMatcher.addURI(PROVIDER_NAME, "anti_aging_items", ANTI_AGING_ITEMS);
38 uriMatcher.addURI(PROVIDER_NAME, "anti_aging_items/#", PRODUCT_ITEM_ID);
39 }
40
41 private DatabaseHelper mDbHelper;
42 private SQLiteDatabase mDb;
43
44 private static final String TAG = "AntiAgingItemsProvider";
45 private static final String DATABASE_NAME = "AntiAgingItems";
46 private static final int DATABASE_VERSION = 1;
47
48 private static final String INGREDIENT_CREATE =
49 "CREATE TABLE " + INGREDIENTS_TABLE + " ( " +
50 "_id INTEGER PRIMARY KEY NOT NULL, " +
51 "name TEXT NOT NULL, " +
52 "about TEXT NOT NULL, " +
53 "category TEXT NOT NULL " +
54 "); ";
55
56 private static final String PRODUCT_CREATE =
57 "CREATE TABLE " + PRODUCTS_TABLE + " ( " +
58 "_id INTEGER PRIMARY KEY NOT NULL AUTO INCREMENT, " +
59 "price REAL, " +
60 "is_favorited NUMERIC, " +
61 "name TEXT, " +
62 "about TEXT, " +
63 "category TEXT, " +
64 "available_at TEXT, " +
65 "award_name TEXT, " +
66 "award_type TEXT, " +
67 "before_after_img TEXT, " +
68 "problems TEXT, " +
69 "body_parts TEXT, " +
70 "test_time TEXT, " +
71 "test_results TEXT, " +
72 "tip TEXT, " +
73 "url TEXT " +
74 "); ";
75
76 private static final String INGREDIENT_PRODUCT_CREATE =
77 "CREATE TABLE ingredient_product ( " +
78 "product_id INTEGER NOT NULL, " +
79 "ingredient_id INTEGER NOT NULL, " +
80 "PRIMARY KEY (product_id, ingredient_id), " +
81 "FOREIGN KEY(product_id) REFERENCES product(_id), " +
82 "FOREIGN KEY(ingredient_id) REFERENCES ingredient(_id) " +
83 "); ";
84
85 private static final String DATABASE_CREATE =
86 INGREDIENT_CREATE +
87 PRODUCT_CREATE +
88 INGREDIENT_PRODUCT_CREATE;
89
90 private final Context mCtx;
91
92
93 public AntiAgingItemsProvider(Context ctx) {
94 this.mCtx = ctx;
95 }
96
97 public AntiAgingItemsProvider open() throws SQLException {
98 mDbHelper = new DatabaseHelper(mCtx);
99 mDb = mDbHelper.getWritableDatabase();
100 return this;
101 }
102
103 public void close() {
104 mDbHelper.close();
105 }
106
107
108 public long insertProduct(ContentValues values) {
109 return mDb.insert(PRODUCTS_TABLE, null, values);
110 }
111
112 public long insertIngredient(ContentValues values) {
113 return mDb.insert(INGREDIENTS_TABLE, null, values);
114 }
115
116 public void createLink(long product_id, long ingredient_id) {
117 ContentValues values = new ContentValues();
118 values.put("ingredient_id", ingredient_id);
119 values.put("product_id", product_id);
120 mDb.insert("ingredient_product", null, values);
121 }
122
123 public boolean deleteRow(long rowId, String database_table) {
124 return mDb.delete(database_table, "_id = " + rowId, null) > 0;
125 }
126
127
128 /* * * * * * * * * * */
129 /* Fetch Ingredients */
130 /* * * * * * * * * * */
131
132 public List<Ingredient> fetchIngredients() {
133 return fetchIngredients(null);
134 }
135
136 /**
137 * @param id ID of row
138 */
139 public List<Ingredient> fetchIngredientsById(long id) {
140 return fetchIngredients("_id = " + id);
141 }
142
143 private List<Ingredient> fetchIngredients(String whereClause) {
144 List<Ingredient> list = new ArrayList<Ingredient>();
145 Cursor c = mDb.query(INGREDIENTS_TABLE, INGREDIENT_STAR, whereClause, null, null, null, null);
146
147 if(c.moveToFirst()) {
148 do {
149 list.add(new Ingredient(c.getLong(0), c.getString(1), c.getString(2), c.getString(3)));
150 } while(c.moveToNext());
151 }
152
153 if (c != null && !c.isClosed()) {
154 c.close();
155 }
156
157 return list;
158 }
159
160 /* * * * * * * * * */
161 /* Fetch Products */
162 /* * * * * * * * * */
163
164 /**
165 * @param type AntiAgingItemsProvider.HAIR or AntiAgingItemsProvider.SKIN
166 * @throws IllegalArgumentException If type is not SKIN or HAIR constant from this class.
167 */
168 public List<Product> fetchProductsByType(String type) {
169 if(type != null && (type.equals(HAIR) || type.equals(SKIN)))
170 return fetchProducts("WHERE category = " + type, "");
171 else
172 throw new IllegalArgumentException();
173 }
174
175 /**
176 * @param id ID of row
177 */
178 public List<Product> fetchProductsById(long id) {
179 return fetchProducts(
180 "WHERE _id = " + id,
181 "");
182 }
183
184 public List<Product> fetchProductByIngredientId(long id) {
185 return fetchProducts(
186 "WHERE _id = " + id,
187
188 "INNER JOIN ingredient_product AS ip" +
189 "ON ip.product_id = products._id" +
190 "INNER JOIN ingredients AS ingredients" +
191 "ON ingredients._id = ip.ingredient_id");
192 }
193
194 private List<Product> fetchProducts(String whereClause, String joinClause) {
195 List<Product> list = new ArrayList<Product>();
196 Cursor c = mDb.rawQuery("SELECT * FROM products " + joinClause + " " + whereClause, null);
197
198 if(c.moveToFirst()) {
199 do {
200 list.add(new Product(c.getLong(0), c.getDouble(1), c.getInt(2) > 0, c.getString(3), c.getString(4), c.getString(5), c.getString(6),
201 c.getString(7), c.getString(8), c.getString(9), c.getString(10), c.getString(11), c.getString(12), c.getString(13),
202 c.getString(14), c.getString(15)));
203
204 } while(c.moveToNext());
205 }
206
207 if (c != null && !c.isClosed()) {
208 c.close();
209 }
210
211 return list;
212 }
213
214 public void setFavorite(long rowId, boolean favorite) {
215 mDb.execSQL("UPDATE products " +
216 "SET is_favorited = " + favorite +
217 "WHERE _id = " + rowId );
218 }
219
220
221 private static class DatabaseHelper extends SQLiteOpenHelper {
222 DatabaseHelper(Context context) {
223 super(context, DATABASE_NAME, null, DATABASE_VERSION);
224 }
225
226 @Override
227 public void onCreate(SQLiteDatabase db) {
228 db.execSQL(DATABASE_CREATE);
229 }
230
231 @Override
232 public void onOpen(SQLiteDatabase db) {
233 super.onOpen(db);
234 if (!db.isReadOnly()) {
235 db.execSQL("PRAGMA foreign_keys = ON;");
236 }
237 }
238
239 @Override
240 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
241 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
242 + newVersion + ", which will destroy all old data");
243 db.execSQL("DROP TABLE IF EXISTS notes");
244 onCreate(db);
245 }
246 }
247}