%line	%branch
ca.spaz.cron.datasource.sql.SQLDatasource	21%	48%

1		/*
2		*******************************************************************************
3		* Copyright (c) 2005 Chris Rose and AIMedia
4		* All rights reserved. SQLDatasource and the accompanying materials
5		* are made available under the terms of the Common Public License v1.0
6		* which accompanies this distribution, and is available at
7		* http://www.eclipse.org/legal/cpl-v10.html
8		*
9		* Contributors:
10		* Chris Rose
11		*******************************************************************************/
12		package ca.spaz.cron.datasource.sql;
13
14		import java.io.*;
15		import java.lang.reflect.Field;
16		import java.util.Date;
17		import java.sql.*;
18		import java.util.*;
19
20		import org.apache.log4j.Logger;
21
22		import ca.spaz.cron.database.*;
23		import ca.spaz.cron.datasource.*;
24		import ca.spaz.sql.*;
25
26		/**
27		* A Food datasource that is based on a SQL database.
28		*
29		* @author Chris Rose
30		*/
31	20	public class SQLDatasource extends AbstractMutableFoodDatasource {
32		/**
33		* Logger for this class
34		*/
35	29	private static final Logger logger = Logger.getLogger(SQLDatasource.class);
36
37		protected Connection conn;
38
39		private boolean allowLocal;
40
41		public static final String FOOD_DB_ID = "food";
42
43		private static final String USER_DB_CONNECTION_ID = "user";
44
45		private String id;
46
47		public static IFoodDatasource createReadonlyFoodSource(String connectionID) {
48	442	Connection conn = ConnectionManager.getInstance(connectionID)
49	182	.getConnection();
50	260	String name = ConnectionManager.getConnectionName(connectionID);
51	260	if (null == conn) {
52	0	throw new IllegalArgumentException("No connection for " + connectionID);
53		}
54	260	return new SQLDatasource(name, connectionID, conn, false);
55		}
56
57		public static ILocalFoodDatasource createPersonalFoodSource() {
58	0	return createPersonalFoodSource(USER_DB_CONNECTION_ID);
59		}
60
61		public static ILocalFoodDatasource createPersonalFoodSource(String connectionId) {
62	442	Connection conn = ConnectionManager.getInstance(connectionId)
63	182	.getConnection();
64	260	String name = ConnectionManager.getConnectionName(connectionId);
65	260	if (null == conn) {
66	0	throw new IllegalArgumentException("No local connection for "
67		+ connectionId);
68		}
69	260	return new SQLDatasource(name, connectionId, conn, true);
70		}
71
72		protected SQLDatasource(String name, String id, Connection conn, boolean allowLocal) {
73	620	super(name);
74	620	this.conn = conn;
75	620	this.setId(id);
76	620	this.allowLocal = allowLocal;
77	620	}
78
79		/*
80		* (non-Javadoc)
81		*
82		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#consumeFood(ca.spaz.cron.database.Food,
83		* ca.spaz.cron.datasource.Serving)
84		*/
85		protected Food doConsumeFood(Serving serving) {
86	2	checkReadWrite();
87	1	validFood(serving.getFood());
88	0	Food ret = serving.getFood();
89	0	if (serving.getFood().getDataSource() != this) {
90	0	ret = addFood(serving.getFood());
91	0	serving.setFood(ret);
92		}
93	0	SQLFood sfood = (SQLFood) ret;
94	0	if (ret == null) {
95	0	return ret;
96		}
97		try {
98		// INSERT INTO Consumed (fid, grams, meal, date) VALUES
99		// ($food.getID(),$grams,$meal,$date);
100	0	SQLInsert ins = new SQLInsert("Serving");
101	0	ins.getColumns().add("fid", sfood.getID());
102	0	int MID = serving.getMeasure().getID();
103	0	if (MID >= 0) {
104	0	ins.getColumns().add("mid", MID);
105		}
106	0	ins.getColumns().add("grams", serving.getGrams());
107	0	ins.getColumns().add("meal", serving.getMeal());
108	0	ins.getColumns().add("eaten", serving.getDate());
109	0	ResultSet rs = ins.executeQuery(conn);
110	0	if (rs != null && rs.next()) {
111		// FIXME: doesn't work
112	0	serving.setID(rs.getInt("id"));
113		}
114		// TODO: get this food from this source.
115	0	} catch (SQLException e) {
116	0	ret = null;
117	0	logger.error("[" + getId() + "] - addToDatabase()", e);
118	0	registerError(e);
119	0	}
120
121	0	return ret;
122		}
123
124		/**
125		*
126		*/
127		private void checkReadWrite() {
128	90	if (!allowLocal) {
129	40	throw new UnsupportedOperationException("Not modifiable");
130		}
131	50	}
132
133		/*
134		* (non-Javadoc)
135		*
136		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#changeConsumedAmount(ca.spaz.cron.database.Food,
137		* ca.spaz.cron.datasource.Serving)
138		*/
139		protected boolean doChangeServingAmount(Serving serving) {
140	20	checkReadWrite();
141	10	validFood(serving.getFood());
142	0	SQLFood sfood = (SQLFood) serving.getFood();
143	0	boolean success = true;
144		try {
145	0	SQLUpdate sql = new SQLUpdate("Serving");
146	0	sql.getColumns().add("grams", serving.getGrams());
147	0	int MID = serving.getMeasure().getID();
148	0	if (MID >= 0) {
149	0	sql.getColumns().add("MID", MID);
150	0	} else {
151	0	sql.getColumns().add("MID", 0); // FIXME: CORRECT? Will this always be GRAMS?
152		}
153	0	sql.addWhere("fid", sfood.getID());
154	0	sql.addWhere("eaten", serving.getDate());
155	0	sql.addWhere("meal", serving.getMeal());
156	0	sql.execute(conn);
157	0	} catch (SQLException e) {
158	0	logger.error("[" + getId() + "] - doChangeConsumedAmount(Serving)", e);
159	0	success = false;
160	0	registerError(e);
161	0	}
162	0	return success;
163		}
164
165		/*
166		* (non-Javadoc)
167		*
168		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#unConsumeFood(ca.spaz.cron.database.Food)
169		*/
170		protected boolean doUnConsumeFood(Serving serving) {
171	20	checkReadWrite();
172	10	validFood(serving.getFood());
173	0	SQLFood sfood = (SQLFood) serving.getFood();
174	0	boolean success = true;
175		try {
176	0	SQLDelete sql = new SQLDelete("Serving");
177	0	sql.addWhere("fid", sfood.getID());
178	0	sql.addWhere("ID", serving.getID());
179	0	sql.addWhere("eaten", serving.getDate());
180	0	sql.execute(conn);
181	0	} catch (SQLException e) {
182	0	logger.error("[" + getId() + "] - doUnConsumeFood(Serving)", e);
183	0	success = false;
184	0	registerError(e);
185	0	}
186	0	if (success) {
187	0	notifyObservers(FoodDataEvent.DSEVENT_SERVING_REMOVED, serving);
188		}
189	0	return success;
190		}
191
192		/**
193		* Update all changes made to a set of measures for the food.
194		* First removes any old Measures no longer referenced in the new list
195		* and then saves or adds all the measures in the list.
196		*/
197		protected boolean doChangeMeasure(Food food, List measures) {
198	12	checkReadWrite();
199	11	validFood(food);
200	10	SQLFood sfood = (SQLFood) food;
201	10	List m = Collections.checkedList(measures, Measure.class);
202	10	boolean success = true;
203
204		// remove all old measures not in new list
205	10	List old = doGetMeasuresFor(sfood);
206	10	old.remove(Measure.GRAM);
207	17	for (Iterator iter = old.iterator(); iter.hasNext();) {
208	0	Measure m1 = (Measure) iter.next();
209	0	boolean found = false;
210	0	for (Iterator iter2 = measures.iterator(); iter2.hasNext();) {
211	0	Measure m2 = (Measure) iter2.next();
212	0	if (m1.getID() == m2.getID()) {
213	0	found = true;
214	0	break;
215		}
216	0	}
217	0	if (!found) {
218	0	doRemoveMeasure(sfood, m1);
219		}
220	0	}
221
222		// save or add all measures
223	17	for (Iterator iter = m.iterator(); iter.hasNext();) {
224	0	Measure meas = (Measure) iter.next();
225	0	boolean oldNot = getNotify();
226	0	setNotify(false);
227	0	if (meas.getID() == -1) {
228	0	doAddMeasure(food, meas);
229	0	} else {
230	0	doSaveMeasure(meas);
231		}
232	0	setNotify(oldNot);
233	0	}
234	10	if (success && getNotclass="keyword">ify()) {
235	0	notifyObservers(FoodDataEvent.DSEVENT_MEASURE_CHANGED);
236		}
237	10	return success;
238		}
239
240		/**
241		* Remove a measure from the database.
242		* Safely modifies all Servings that reference this measure,
243		* converting them to canonical GRAMS Measures.
244		* @param m the Measure to delete
245		* @return true if successful
246		*/
247		protected boolean doRemoveMeasure(Food food, Measure m) {
248	2	checkReadWrite();
249	1	validFood(food);
250	0	boolean success = true;
251		try {
252		// remove referece to Measure from all Servings
253	0	SQLUpdate sel = new SQLUpdate("Serving");
254	0	sel.getColumns().add("MID", -1);
255	0	sel.addWhere("MID", new Integer(m.getID()));
256	0	sel.execute(conn);
257
258		// delete Measure
259	0	SQLDelete sql = new SQLDelete("Measure");
260	0	sql.addWhere("ID", m.getID());
261	0	sql.execute(conn);
262	0	} catch (SQLException e) {
263	0	logger.error("[" + getId() + "] - doDeleteMeasure(Measure)", e);
264	0	success = false;
265	0	registerError(e);
266	0	}
267	0	if (success && getNotclass="keyword">ify()) {
268	0	notifyObservers(FoodDataEvent.DSEVENT_MEASURE_REMOVED);
269		}
270	0	return success;
271		}
272
273		/*
274		* (non-Javadoc)
275		*
276		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#addServing(ca.spaz.cron.database.Food,
277		* ca.spaz.cron.datasource.Serving)
278		*/
279		protected boolean doAddMeasure(Food food, Measure measure) {
280	2	checkReadWrite();
281	1	validFood(food);
282	0	SQLFood sfood = (SQLFood) food;
283	0	boolean success = true;
284		try {
285	0	SQLInsert sql = new SQLInsert("Measure");
286	0	sql.getColumns().add("FID", sfood.getID());
287	0	sql.getColumns().add("amount", measure.getAmount());
288	0	sql.getColumns().add("grams", measure.getGrams());
289	0	sql.getColumns().add("description", measure.getDescription());
290	0	ResultSet rs = sql.executeQuery(conn);
291	0	if (rs != null && rs.next()) {
292		// FIXME: doesn't work
293	0	measure.setID(rs.getInt("id"));
294		}
295	0	} catch (SQLException e) {
296	0	logger.error("[" + getId() + "] - addMeasure(Food, Measure)", e);
297	0	success = false;
298	0	registerError(e);
299	0	}
300	0	if (success && getNotclass="keyword">ify()) {
301	0	notifyObservers(FoodDataEvent.DSEVENT_MEASURE_ADDED, measure);
302		}
303	0	return success;
304		}
305
306
307		/*
308		* (non-Javadoc)
309		*
310		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#addServing(ca.spaz.cron.database.Food,
311		* ca.spaz.cron.datasource.Serving)
312		*/
313		protected boolean doSaveMeasure(Measure measure) {
314	0	assert(measure.getID() >= 0);
315	0	checkReadWrite();
316	0	boolean success = true;
317		try {
318	0	SQLUpdate sql = new SQLUpdate("Measure");
319	0	sql.getColumns().add("amount", measure.getAmount());
320	0	sql.getColumns().add("grams", measure.getGrams());
321	0	sql.getColumns().add("description", measure.getDescription());
322	0	sql.addWhere("ID", measure.getID());
323	0	sql.execute(conn);
324	0	} catch (SQLException e) {
325	0	logger.error("[" + getId() + "] - addMeasure(Food, Measure)", e);
326	0	success = false;
327	0	registerError(e);
328	0	}
329	0	if (success && getNotclass="keyword">ify()) {
330	0	notifyObservers(FoodDataEvent.DSEVENT_MEASURE_ADDED, measure);
331		}
332	0	return success;
333		}
334
335		/*
336		* (non-Javadoc)
337		*
338		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#getTimesConsumed(ca.spaz.cron.database.Food)
339		*/
340		protected int doGetTimesConsumed(Food food) {
341	2	checkReadWrite();
342	1	validFood(food);
343	0	SQLFood sfood = (SQLFood) food;
344	0	int num = -1;
345		try {
346		// SELECT COUNT(FID) from Consumed WHERE FID='$getID()';
347	0	SQLSelect s = new SQLSelect("Serving");
348	0	s.addSelection("COUNT(FID)");
349	0	s.addWhere("FID", sfood.getID());
350	0	ResultSet res = s.executeQuery(conn);
351	0	if (res.next()) {
352	0	num = res.getInt(1);
353		}
354	0	} catch (SQLException e) {
355	0	logger.error("[" + getId() + "] - getTimesConsumed(Food, Date, Date)", e);
356	0	registerError(e);
357	0	}
358	0	return num;
359		}
360
361		/*
362		* (non-Javadoc)
363		*
364		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#getTimesConsumed(ca.spaz.cron.database.Food,
365		* java.util.Date, java.util.Date)
366		*/
367		protected int doGetTimesConsumed(Food food, Date startDate, Date endDate) {
368	0	checkReadWrite();
369	0	validFood(food);
370	0	SQLFood sfood = (SQLFood) food;
371	0	int num = -1;
372		try {
373		// SELECT COUNT(FID) from Consumed WHERE FID='$getID()';
374	0	SQLSelect s = new SQLSelect("Serving");
375	0	s.addSelection("COUNT(FID)");
376	0	s.addWhere("FID", sfood.getID());
377	0	s.addWhere("eaten", SQLSelect.GT, startDate);
378	0	s.addWhere("eaten", SQLSelect.LT, endDate);
379	0	ResultSet res = s.executeQuery(conn);
380	0	if (res.next()) {
381	0	num = res.getInt(1);
382		}
383	0	} catch (SQLException e) {
384	0	logger.error("[" + getId() + "] - getTimesConsumed(Food, Date, Date)", e);
385	0	registerError(e);
386	0	}
387	0	return num;
388		}
389
390		/*
391		* (non-Javadoc)
392		*
393		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#getConsumedOn(java.util.Date)
394		*/
395		public List getConsumedOn(Date date) {
396	20	notNull(date);
397	1	checkReadWrite();
398	1	ArrayList res = new ArrayList();
399	1	SQLSelect sel = new SQLSelect("Serving");
400	0	sel.addWhere("eaten", date);
401	0	if (logger.isDebugEnabled()) {
402	0	logger.debug("[" + getId() + "] - getAllConsumedDuringDay(Date) " + sel.toString());
403		}
404		try {
405	0	ResultSet row = sel.executeQuery(conn);
406	0	while (row.next()) {
407	0	Serving c = new Serving(getFoodByID(row.getInt("FID")));
408	0	c.setGrams(row.getDouble("grams"));
409	0	c.setMeasure(row.getInt("MID"));
410	0	c.setID(row.getInt("ID"));
411	0	c.setDate(row.getDate("eaten"));
412	0	c.setMeal(row.getInt("meal"));
413	0	res.add(c);
414	0	}
415	0	} catch (SQLException e) {
416	0	logger.error("[" + getId() + "] - getAllConsumedDuringDay(Date)", e);
417	0	registerError(e);
418	0	}
419	0	return res;
420		}
421
422		private Food getFoodByID(int FID) throws SQLException {
423	0	Food f = null;
424	0	SQLSelect s = new SQLSelect("Food");
425	0	s.addWhere("id", new Integer(FID));
426	0	ResultSet res = s.executeQuery(conn);
427	0	if (res.next()) {
428	0	f = internalCreateFood();
429	0	SQLFood sfood = (SQLFood) f;
430	0	sfood.setID(res.getInt("ID"));
431	0	f.setSource(res.getString("source"));
432	0	f.setDescription(res.getString("description"));
433	0	f.setSourceUID(res.getString("sourceUID"));
434	0	SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
435	0	f.setFoodGroup(fg);
436		}
437	0	return f;
438		}
439
440		/*
441		* (non-Javadoc)
442		*
443		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#addFood(ca.spaz.cron.database.Food)
444		*/
445	4	protected Food doAddFood(Food food) {
446	2	//FIXME Must handle new foods, too.
447	2	checkReadWrite();
448	2	boolean success = true;
449	0	ResultSet genKeys = null;
450	2	int newID = -1;
451	0	try {
452	0	addFoodGroup(food.getFoodGroup());
453	0	SQLInsert sql = new SQLInsert("Food");
454	0	sql.getColumns().add("description", food.getDescription());
455	0	sql.getColumns().add("foodgroup", food.getFoodGroup());
456	0	sql.getColumns().add("source", getName());
457	0	sql.getColumns().add("sourceUID", food.getSourceUID());
458	0	genKeys = sql.executeQuery(conn);
459	0	if (genKeys != null && genKeys.next()) {
460	0	// FIXME: Doesn't work!
461	0	newID = genKeys.getInt("id");
462	0	} else {
463	0	SQLSelect sel = new SQLSelect("Food");
464	0	sel.addWhere("sourceUID", food.getSourceUID());
465	0	sel.addSelection("id");
466	0	sel.addOrderBy("id");
467	0	ResultSet res = sel.executeQuery(conn);
468	0	while (res.next()) {
469	0	newID = res.getInt("id");
470	0	}
471	0	}
472	0	} catch (SQLException e) {
473	0	logger.error("[" + getId() + "] - addFood(Food)", e);
474	0	registerError(e);
475	0	success = false;
476	0	}
477	0	SQLFood newFood = null;
478	0	if (success) {
479	0	try {
480	0	newFood = (SQLFood) createNewFood();
481	0	newFood.setDescription(food.getDescription());
482	0	newFood.setFoodGroup(food.getFoodGroup());
483	0	newFood.setID(newID);
484	0	newFood.setSource(food.getSource());
485	0	newFood.setSourceUID(food.getSourceUID());
486	0	// Copying over the nutrients from the old food.
487	0	addNutrients(newFood, food.getAminoAcids());
488	0	addNutrients(newFood, food.getMacroNutrients());
489	0	addNutrients(newFood, food.getMinerals());
490	0	addNutrients(newFood, food.getVitamins());
491	0	addNutrients(newFood, food.getLipids());
492	0	for (int i=0; i<food.getMeasures().size(); i++) {
493	0	Measure m = (Measure)food.getMeasures().get(i);
494	0	if (m != Measure.GRAM) {
495	0	addMeasure(newFood, m);
496	0	}
497	0	}
498	0	notifyObservers(FoodDataEvent.DSEVENT_FOOD_ADDED, food);
499	0	} catch (SQLException e) {
500	0	logger.error("[" + getId() + "] - doAddFood(Food)", e);
501	0	success = false;
502	0	newFood = null;
503	0	registerError(e);
504	0	}
505		}
506	0	return newFood;
507		}
508
509		/*
510		* (non-Javadoc)
511		*
512		* @see ca.spaz.cron.datasource.ILocalFoodDatasource#changeFood(ca.spaz.cron.database.Food,
513		* ca.spaz.cron.database.Food)
514	2	*/
515	1	protected boolean doSaveFood(Food food) {
516	0	checkReadWrite();
517	0	validFood(food);
518	0	boolean success = true;
519	0	SQLFood sfood = (SQLFood) food;
520	0	if (sfood.getID() < 0) {
521		// Add the food to the database.
522	0	Food ret = doAddFood(sfood);
523	0	success = (ret != null);
524	0	} else {
525		// Save the existing food.
526	0	addFoodGroup(food.getFoodGroup());
527		try {
528	0	SQLUpdate sql = new SQLUpdate("Food");
529	0	sql.getColumns().add("description", sfood.getDescription());
530	0	sql.getColumns().add("foodgroup", sfood.getFoodGroup());
531	0	sql.getColumns().add("source", sfood.getSource());
532	0	sql.getColumns().add("sourceUID", sfood.getSourceUID());
533	0	sql.addWhere("ID", Integer.toString(sfood.getID()));
534	0	sql.execute(conn);
535	0	saveNutrients(sfood, sfood.getAminoAcids());
536	0	saveNutrients(sfood, sfood.getMacroNutrients());
537	0	saveNutrients(sfood, sfood.getMinerals());
538	0	saveNutrients(sfood, sfood.getVitamins());
539	0	saveNutrients(sfood, sfood.getLipids());
540	0	changeMeasure(sfood, sfood.getMeasures());
541	0	} catch (SQLException e) {
542	0	logger.error("[" + getId() + "] - changeFood(Food)", e);
543	0	success = false;
544	0	registerError(e);
545	0	}
546		}
547	0	if (success) {
548	0	notifyObservers(FoodDataEvent.DSEVENT_FOOD_CHANGED, sfood);
549		}
550	0	return success;
551		}
552
553		private void saveNutrients(SQLFood food, NutrientTable table)
554		throws SQLException {
555	0	SQLUpdate sql = new SQLUpdate(table.getTableName());
556	0	Field[] fields = table.getClass().getFields();
557	0	for (int i = 0; i < fields.length; i++) {
558		try {
559	0	sql.getColumns().add(fields[i].getName(), fields[i].getDouble(table));
560	0	} catch (Exception e) {
561	0	logger.error("[" + getId() + "] - saveNutrients(Food,NutrientTable)", e);
562	0	registerError(e);
563	0	}
564		}
565	0	sql.addWhere("FID", food.getID());
566	0	sql.execute(conn);
567	0	}
568
569		/**
570		* Add nutrients table if the table does not yet exist
571		* @param food the food to which the nutrients are linked
572		* @param table the nutrient table to add to the food
573		* @throws SQLException
574		*/
575		private void addNutrients(SQLFood food, NutrientTable table) throws SQLException {
576	0	SQLInsert sql = new SQLInsert(table.getTableName());
577	0	Field[] fields = table.getClass().getFields();
578	0	for (int i = 0; i < fields.length; i++) {
579		try {
580	0	sql.getColumns().add(fields[i].getName(), fields[i].getDouble(table));
581	0	} catch (Exception e) {
582	0	logger.error("[" + getId() + "] - saveNutrients(Food,NutrientTable)", e);
583	0	registerError(e);
584	0	}
585		}
586	0	sql.getColumns().add("FID", food.getID());
587	0	sql.execute(conn);
588	10	}
589	8
590		private void validFood(Food f) {
591	38	if (!(f instanceof SQLFood)) {
592	18	throw new IllegalArgumentException("SQL Datasource requires SQL food objects");
593		}
594	18	}
595
596		/*
597		* (non-Javadoc)
598		*
599	2	* @see ca.spaz.cron.datasource.ILocalFoodDatasource#removeFood(ca.spaz.cron.database.Food)
600	1	*/
601		protected boolean doRemoveFood(Food food) {
602	0	checkReadWrite();
603	0	validFood(food);
604	0	SQLFood sfood = (SQLFood) food;
605	0	boolean success = true;
606		try {
607		// @TODO: can't until all measures and servings are removed first
608	0	SQLDelete sql = new SQLDelete("Food");
609	0	sql.addWhere("id", sfood.getID());
610	0	sql.execute(conn);
611	0	} catch (SQLException e) {
612	0	logger.error("[" + getId() + "] - removeFood(Food) - ", e);
613	0	success = false;
614	0	registerError(e);
615	0	}
616	0	if (success) {
617	0	notifyObservers(FoodDataEvent.DSEVENT_FOOD_DELETED, sfood);
618		}
619	0	return success;
620		}
621
622		/*
623		* (non-Javadoc)
624		*
625		* @see ca.spaz.cron.datasource.IFoodDatasource#findFood(java.lang.String[])
626		*/
627		protected List doFindFoods(String[] parts) {
628	0	return doFindFoods(parts, null, class="keyword">null);
629		}
630
631		/*
632		* (non-Javadoc)
633		*
634		* @see ca.spaz.cron.datasource.IFoodDatasource#findFood(java.lang.String,
635		* java.lang.String, java.lang.String)
636		*/
637		protected List doFindFoods(String[] keys, String foodGroup, String source) {
638	0	List result = new ArrayList();
639	0	SQLSelect select = new SQLSelect("Food");
640
641		//if (parts.length == 0) return;
642
643	0	for (int j = 0; j < keys.length; j++) {
644	0	if (keys[j].length() > 0) {
645	0	if (logger.isDebugEnabled()) {
646	0	logger.debug("[" + getId() + "] - doDBSearch() - Split part: " + keys[j]);
647		}
648	0	select.addWhereLike("description", "%" + keys[j] + "%");
649	0	select.addWhere("source", getName());
650		}
651		}
652
653	0	if (null != foodGroup) {
654	0	select.addWhere("foodgroup", foodGroup);
655		}
656
657	0	if (null != source) {
658	0	select.addWhere("source", source);
659		}
660
661	0	if (logger.isDebugEnabled()) {
662	0	logger.debug("[" + getId() + "] - doDBSearch() - Query: " + select.toString());
663		}
664
665		try {
666	0	ResultSet res = select.executeQuery(conn);
667	0	result.clear();
668	0	buildFoodList(result, res);
669	0	} catch (SQLException e) {
670	0	logger.error("[" + getId() + "] - doDBSearch()", e);
671	0	registerError(e);
672	0	}
673	0	return result;
674		}
675
676		/**
677		* @param result
678		* @param res
679		* @throws SQLException
680		*/
681		private void buildFoodList(List result, ResultSet res) throws SQLException {
682	0	while (res.next()) {
683	0	Food f = internalCreateFood();
684	0	SQLFood sfood = (SQLFood) f;
685	0	sfood.setID(res.getInt("ID"));
686	0	f.setSource(res.getString("source"));
687	0	f.setDescription(res.getString("description"));
688	0	f.setSourceUID(res.getString("sourceUID"));
689	0	SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
690	0	f.setFoodGroup(fg);
691	0	result.add(f);
692	0	}
693	0	}
694
695		/*
696		* (non-Javadoc)
697		*
698		* @see ca.spaz.cron.datasource.IFoodDatasource#findAllFoods()
699		*/
700		protected List doFindAllFoods() {
701	0	List result = new ArrayList();
702	0	SQLSelect sel = new SQLSelect("Food");
703	0	sel.addWhere("source", getName());
704		try {
705	0	ResultSet res = sel.executeQuery(conn);
706	0	buildFoodList(result, res);
707	0	} catch (SQLException e) {
708	0	logger.error("[" + getId() + "] - doFindAllFoods()", e);
709	0	registerError(e);
710	0	}
711	0	return result;
712		}
713
714		/*
715		* (non-Javadoc)
716	1	*
717	1	* @see ca.spaz.cron.datasource.IFoodDatasource#getServingsFor(ca.spaz.cron.database.Food)
718	1	*/
719	1	protected List doGetMeasuresFor(Food food) {
720	9	ArrayList weights = new ArrayList();
721	10	weights.add(Measure.GRAM);
722	10	validFood(food);
723	10	SQLFood sfood = (SQLFood) food;
724	2	try {
725	9	SQLSelect s = new SQLSelect("Measure");
726	9	s.addWhere("FID", new Integer(sfood.getID()));
727	9	ResultSet res = s.executeQuery(conn);
728	15	while (res.next()) {
729	0	Measure w = new Measure();
730	0	w.setFoodID(sfood.getID());
731	0	w.setID(res.getInt("ID"));
732	0	w.setAmount(res.getDouble("amount"));
733	0	w.setDescription(res.getString("description"));
734	0	w.setGrams(res.getDouble("grams"));
735	0	weights.add(w);
736	0	}
737	0	} catch (SQLException e) {
738	1	logger.error("[" + getId() + "] - getMeasuresFor(Food)", e);
739	0	registerError(e);
740	3	}
741
742	9	return weights;
743		}
744
745		/*
746		* (non-Javadoc)
747		*
748		* @see ca.spaz.cron.datasource.IFoodDatasource#getNutrientsFor(ca.spaz.cron.database.Food)
749		*/
750		protected void doGetNutrientsFor(Food food, NutrientTable nutrients) {
751	0	validFood(food);
752	0	SQLSelect s = new SQLSelect(nutrients.getTableName());
753	0	SQLFood sfood = (SQLFood) food;
754	0	s.addWhere("FID", new Integer(sfood.getID()));
755		try {
756	0	ResultSet res = s.executeQuery(conn);
757	0	if (res.next()) {
758		try {
759	0	DBRow.load(res, nutrients);
760	0	} catch (Exception e) {
761	0	logger.error("NutrientTable(Food)", e);
762	0	registerError(e);
763	0	}
764		}
765	0	} catch (SQLException e) {
766	0	logger.error("[" + getId() + "] - getNutrientsFor() - food: " + food.toString()
767		+ ", nutrients: " + nutrients.toString(), e);
768	0	registerError(e);
769	0	}
770
771	0	}
772
773		/*
774		* (non-Javadoc)
775		*
776		* @see ca.spaz.cron.datasource.IFoodDatasource#getSources()
777		*/
778		public List getSources() {
779	0	List sources = new ArrayList();
780		try {
781	0	SQLSelect s = new SQLSelect("Food");
782	0	s.addSelection("DISTINCT(source)");
783	0	ResultSet res = s.executeQuery(conn);
784	0	while (res.next()) {
785	0	sources.add(res.getString("source"));
786	0	}
787	0	} catch (SQLException e) {
788	0	logger.error("[" + getId() + "] - getAllSources()", e);
789	0	registerError(e);
790	0	}
791	0	return sources;
792		}
793
794		/*
795		* (non-Javadoc)
796		*
797		* @see ca.spaz.cron.datasource.IFoodDatasource#getFoodGroups()
798		*/
799		public List getFoodGroups() {
800	0	List groups = new ArrayList();
801		try {
802	0	SQLSelect s = new SQLSelect("Food");
803	0	s.addSelection("DISTINCT(foodgroup)");
804	0	ResultSet res = s.executeQuery(conn);
805	0	while (res.next()) {
806	0	SQLFoodGroup fg = new SQLFoodGroupImpl(res.getString("foodgroup"));
807	0	groups.add(fg);
808	0	}
809	0	} catch (SQLException e) {
810	0	logger.error("[" + getId() + "] - getAllFoodGroups()", e);
811	0	registerError(e);
812	0	}
813	0	return groups;
814		}
815
816		/*
817		* (non-Javadoc)
818		*
819		* @see ca.spaz.cron.datasource.IFoodDatasource#isSearchable()
820		*/
821		public boolean isSearchable() {
822	0	return true;
823		}
824
825		/*
826		* (non-Javadoc)
827	64	*
828		* @see ca.spaz.cron.datasource.IFoodDatasource#isAvailable()
829	64	*/
830	37	public boolean isAvailable() {
831	1026	boolean ret = true;
832	63	try {
833	1026	if (allowLocal) {
834	558	conn.createStatement().execute("SELECT * FROM Measure");
835	2	}
836	794	if (!conn.createStatement().execute("SELECT * FROM Food")) {
837	0	ret = false;
838	64	}
839	468	} catch (SQLException e) {
840	468	ret = false;
841	186	}
842	1026	return ret;
843		}
844
845		/*
846		* (non-Javadoc)
847		*
848		* @see ca.spaz.cron.datasource.IFoodDatasource#isListable()
849		*/
850		public boolean isListable() {
851	0	return true;
852		}
853
854	2	/* (non-Javadoc)
855	1	* @see ca.spaz.cron.datasource.AbstractMutableFoodDatasource#doCreateNewFood()
856		*/
857		protected Food doCreateNewFood() {
858	18	checkReadWrite();
859	10	return internalCreateFood();
860		}
861
862		private Food internalCreateFood() {
863	9	return new SQLFoodImpl(this);
864		}
865
866		/* (non-Javadoc)
867	52	* @see ca.spaz.cron.datasource.IFoodDatasource#close()
868	52	*/
869		public void close() {
870		try {
871	468	if (!conn.isClosed()) {
872	520	if (conn.getMetaData().getURL().startsWith("jdbc:hsqldb:file") ||
873	364	conn.getMetaData().getURL().startsWith("jdbc:hsqldb:mem")) {
874	468	conn.createStatement().execute("SHUTDOWN");
875	468	logger.info("[" + getId() + "] - Shutdown called");
876		}
877	468	conn.close();
878	468	logger.info("[" + getId() + "] - closed");
879	52	}
880	0	} catch (SQLException e) {
881	0	logger.error("[" + getId() + "] - close()", e);
882	0	registerError(e);
883	156	}
884	468	}
885
886		/* (non-Javadoc)
887		* @see ca.spaz.cron.datasource.IFoodDatasource#initialize()
888		*/
889		public void initialize() {
890		/* Retrieve a resource stream matching the datasource ID from the /sql/{resource}
891		* classpath. Read each line one by one, executing them. If any error occurs
892	12	* during this process, throw an IllegalStateException. If, upon successful
893		* execution of these statements, isAvailable is false, throw an IllegalStateException
894		*
895		* In the SQL, ignore ALL lines that begin with "--"
896	12	*/
897	570	if (logger.isInfoEnabled()) {
898	0	logger.info("Initializing " + getId() + ".");
899		}
900	12	try {
901	570	InputStream resource = getClass().getResourceAsStream("/sql/" + getId() + ".sql");
902	558	if (null == resource) {
903	12	throw new FileNotFoundException("/sql/" + getId() + ".sql");
904	12	}
905	1120	BufferedReader br = new BufferedReader(class="keyword">new InputStreamReader(resource));
906	1096	String line = null;
907	538	try {
908	910	Statement stmt = conn.createStatement();
909	833	line = br.readLine();
910	21072	while (line != null) {
911	20142	line = line.trim();
912	20680	if (line.length() > 0) { // If it's not an empty line
913	12618	if (!line.startsWith("--")) { // if it's not a comment
914	10350	stmt.execute(line);
915		}
916		}
917	20142	line = br.readLine();
918	6714	}
919	0	} catch (SQLException e) {
920	12	throw new IllegalStateException("Unable to execute '" + line + "'", e);
921	186	}
922	0	} catch (IOException e) {
923	12	throw new IllegalStateException("Unable to retrieve resource for initializing " + this.getName(), e);
924	186	}
925	558	if (!isAvailable()) {
926	0	throw new IllegalStateException("Successful initialization, but " + getName() + " is uninitialized.");
927		}
928	558	}
929
930		/* (non-Javadoc)
931		* @see ca.spaz.cron.datasource.AbstractMutableFoodDatasource#doAddFoodGroup(ca.spaz.cron.database.FoodGroup)
932		*/
933		protected void doAddFoodGroup(FoodGroup foodGroup) {
934	0	checkReadWrite();
935		try {
936	0	SQLSelect sel = new SQLSelect("FoodGroup");
937	0	sel.addWhere("fg_name", foodGroup.getFoodGroupName());
938	0	ResultSet res = sel.executeQuery(conn);
939	0	if (res.next()) {
940		// Already exists
941	0	return;
942		}
943	0	} catch (SQLException e) {
944	0	logger.error("doAddFoodGroup(FoodGroup) - Unable to verify food group", e);
945	0	}
946	0	SQLInsert ins = new SQLInsert("FoodGroup");
947	0	ins.getColumns().add("fg_name", foodGroup.getFoodGroupName());
948		try {
949	0	ins.execute(conn);
950	0	} catch (SQLException e) {
951	0	logger.error("doAddFoodGroup(FoodGroup) - unable to add food group", e);
952	0	}
953	0	}
954
955	62	/**
956	62	* Set the <code>id</code> in the <code>SQLDatasource</code>.
957		* @param id The id to set.
958		*/
959		public void setId(String id) {
960	558	this.id = id;
961	558	}
962
963		/**
964		* Retrieve the <code>id</code> from the <code>SQLDatasource</code>. This will be
965	84	* unique between all SQL data source instances.
966		*
967		* @return Returns the id.
968		*/
969		public String getId() {
970	1683	return id;
971		}
972
973		protected Food findFoodBySourceUID(String sourceUID) {
974	0	SQLSelect sel = new SQLSelect("Food");
975	0	sel.addWhere("SourceUID", sourceUID);
976	0	sel.addSelection("ID");
977		ResultSet res;
978	0	Food ret = null;
979		try {
980	0	res = sel.executeQuery(conn);
981	0	if (!res.next()) {
982	0	ret = null;
983	0	} else {
984	0	int fid = res.getInt("ID");
985	0	ret = getFoodByID(fid);
986		}
987	0	} catch (SQLException e) {
988		// TODO Auto-generated catch block
989	0	e.printStackTrace();
990	0	}
991	0	return ret;
992		}
993
994		public boolean containsFood(Food food) {
995	0	SQLSelect sel = new SQLSelect("Food");
996	0	sel.addWhere("SourceUID", food.getSourceUID());
997	0	boolean return_value = false;
998		try {
999	0	ResultSet res = sel.executeQuery(conn);
1000	0	return_value = res.next();
1001	0	res.close();
1002	0	} catch (SQLException e) {
1003	0	logger.error("containsFood(Food)", e); //$NON-NLS-1$
1004	0	}
1005	0	return class="keyword">return_value;
1006		}
1007
1008		}

This report is generated by jcoverage, Maven and Maven JCoverage Plugin.