Coverage Report

 /******************************************************************************
  * FiltersDaoJdbc.java - Implement the Dao interface for the Filters
  * 
  * PicMan - The BuckoSoft Picture Manager in Java
  * Copyright(c) 2005 - Dick Balaska
  * 
  */
 package com.buckosoft.PicMan.db;
 
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Types;
 import java.util.Iterator;
 import java.util.LinkedHashMap;
 import java.util.LinkedList;
 import java.util.List;
 
 import javax.sql.DataSource;
 
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.springframework.jdbc.core.SqlParameter;
 import org.springframework.jdbc.object.MappingSqlQuery;
 import org.springframework.jdbc.object.SqlFunction;
 import org.springframework.jdbc.object.SqlUpdate;
 
 import com.buckosoft.PicMan.domain.Filter;
 import com.buckosoft.PicMan.domain.MetaSet;
 import com.buckosoft.PicMan.domain.Set;
 
 /** Implement the Dao interface for the {@link com.buckosoft.PicMan.domain.Filter}s.
  * @author Dick Balaska
  * @since 2005/07/30
  * @see com.buckosoft.PicMan.domain.Filter
  * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/FiltersDaoJdbc.java">FiltersDaoJdbc.java</a>
  */
 public class FiltersDaoJdbc implements FiltersDao {
 
         private final static boolean DEBUG = false;
         private final static boolean DEBUGCONNECTIONLEAK = false;
         protected final Log logger = LogFactory.getLog(getClass());
         
         private        LinkedList<String>        filterColumns;
         
         private DataSource ds;
         
         /** Set the JDBC datasource reference
      * @param ds The DataSource as generated by the Spring config/setup.
      */
         public void setDataSource(DataSource ds) {
                 this.ds = ds;
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#addSet(java.lang.String, int)
          */
         public        void addSet(String name, int size) {
                 String n = name;
                 if (size > 0)
                         n += "_" + size;
                 String s = "ALTER TABLE `filters` ADD `" + n + "` TINYINT DEFAULT '0' NOT NULL";
                 if (DEBUG)
                         logger.info(s);
                 SqlUpdate sf = new SqlUpdate(ds, s);
                 sf.update();
                 filterColumns = null;
         }
 
         public void renameSet(String oldName, String newName, int size) {
                 oldName += "_" + size;
                 newName += "_" + size;
                 String s = "ALTER TABLE `filters` CHANGE `" + oldName + "` `" + newName + "` TINYINT(4) NOT NULL DEFAULT '0'";
                 if (DEBUG)
                         logger.info(s);
                 SqlUpdate sf = new SqlUpdate(ds, s);
                 sf.update();
                 filterColumns = null;
                 
         }
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#deleteSet(java.lang.String, int)
          */
         public void deleteSet(String name, int size) {
                 String n = name;
                 if (size > 0)
                         n += "_" + size;
                 String s = "ALTER TABLE `filters` DROP `" + n + "`";
                 if (DEBUG)
                         logger.info(s);
                 try {
                         SqlUpdate sf = new SqlUpdate(ds, s);
                         sf.update();
                 } catch (Exception e) {
                 }
                 filterColumns = null;
         }
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getFilters()
          */
         @SuppressWarnings("unchecked")
         public List<Filter>                getFilters() {
                 if (filterColumns == null)
                         getFilterColumns();
                 FilterQuery fq = new FilterQuery(ds);
                 return(fq.execute());
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getFiltersBySet(java.lang.String, int)
          */
         @SuppressWarnings("unchecked")
         public         List<Filter>        getFiltersBySet(String setName, int size) {
                 if (filterColumns == null)
                         getFilterColumns();
                 String        s = setName + "_" + size;
                 FilterSetQuery fq = new FilterSetQuery(ds, s);
                 return(fq.execute());
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getPicNamesBySet(java.lang.String, int)
          */
         @SuppressWarnings("unchecked")
         public        List<String>        getPicNamesBySet(Set set, int size) {
                 if (filterColumns == null)
                         getFilterColumns();
                 String s = "filters." + set.getName() + "_" + size;
                 if (DEBUG)
                         logger.info("getPicNamesBySet '" + s + "'");
                 if (sql_getPicNamesBySetQUERY == null)
                         sql_getPicNamesBySetQUERY = new FilterSetNameQuery(ds, s);
                 FilterSetNameQuery fq = new FilterSetNameQuery(ds, s);
                 return(fq.execute());
         }
         private        FilterSetNameQuery        sql_getPicNamesBySetQUERY;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getPicNamesBySet(java.lang.String, int, int, int)
          */
         @SuppressWarnings("unchecked")
         public        List<String>        getPicNamesBySet(Set set, int size, int rateOp, int rateVal) {
                 if (rateOp == 0)
                         return(getPicNamesBySet(set, size));
                 if (filterColumns == null)
                         getFilterColumns();
                 String s = "filters." + set.getName() + "_" + size;
                 if (DEBUG)
                         logger.info("getPicNamesBySet '" + s + "'");
                 if (sql_getPicNamesBySetRVQUERY == null)
                         sql_getPicNamesBySetRVQUERY = new FilterSetNameQuery(ds, s, rateOp, rateVal);
                 FilterSetNameQuery fq = new FilterSetNameQuery(ds, s, rateOp, rateVal);
                 //return(fq.execute(new Object[] {MetaSet.rateOps[rateOp], new Integer(rateVal)}));
                 return(fq.execute());
         }
         private        FilterSetNameQuery        sql_getPicNamesBySetRVQUERY;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getFilter(java.lang.String)
          */
         public Filter        getFilter(String picName) {
                 if (filterColumns == null)
                         getFilterColumns();
                 if (DEBUG)
                         logger.info("getFilter: " + picName);
                 if (sql_getFilterSELECTr == null)
                         sql_getFilterSELECTr = new FilterQuery(ds, picName);
                 Filter f = (Filter)sql_getFilterSELECTr.findObject(picName); 
                 if (f != null)
                         return(f);
                 f = new Filter();
                 logger.warn("Creating new filter for pic " + picName);
                 f.setPicName(picName);
                 Iterator<String> i = filterColumns.iterator();
                 String        s;
                 while (i.hasNext()) {
                         s = (String)i.next();
                         if (s.equals("date"))
                                 continue;
                         f.addFilter(s, 0);
                 }
                 return(f);
         }
         private FilterQuery sql_getFilterSELECTr;
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getFilterColumns()
          */
         public        List<String>        getFilterColumns() {
                 if (filterColumns != null)
                         return(filterColumns);
                 if (DEBUG)
                         logger.info("getFilterColumns()");
                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                 fcq.execute();
                 return(filterColumns);                // ugly
         }
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#addFilter(com.buckosoft.PicMan.domain.Filter)
          */
         public        void        addFilter(Filter filter) {
                 String        s;
                 if (filterColumns == null)
                         getFilterColumns();
                 if (sql_addFilterDELETEr == null)
                         sql_addFilterDELETEr = new FilterDelete(ds, "DELETE FROM filters WHERE pic = ?");
 
                 s = "DELETE FROM filters WHERE pic = \"" + filter.getPicName() + "\"";
                 if (DEBUG)
                         logger.info(s);
                 SqlUpdate sf = new SqlUpdate(ds, s);
                 sf.update();
                 
                 String        key;
                 s = "`pic`,`date`";
                 String        t = "'" + filter.getPicName() + "','" + new java.sql.Timestamp(filter.getDate().getTime()).toString() + "'" ;
                 LinkedHashMap<String, Integer> map = filter.getFilters();
                 Iterator<String> it = map.keySet().iterator();
                 while (it.hasNext()) {
                         key = (String)it.next();
                         if (key.indexOf('_') == -1)                // don't include microSets in this sql update
                                 continue;
                         s += ",`" + key + "`";
                         t += ",'" + map.get(key) + "'";
                 }
                 if (DEBUG)
                         logger.info("s=" + s + " t=" + t);
                 SqlUpdate su;
                 su = new SqlUpdate(ds, "INSERT INTO filters (" + s + ") VALUES(" + t + ")");
                 su.update();
         }
         private        SqlUpdate        sql_addFilterDELETEr;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.FiltersDao#getFilterCount()
          */
         public int        getFilterCount() {
                 if (DEBUGCONNECTIONLEAK)
                         return(269);
                 if (sql_getFilterCountCOUNTr == null)
                         sql_getFilterCountCOUNTr = new FiltersCount(ds);
                 if (DEBUG)
                         logger.info("getFilterCount:");
                 return(sql_getFilterCountCOUNTr.run());
         }
         private FiltersCount        sql_getFilterCountCOUNTr;
 
         /**
          * <code>Filter</code> Count Object.
          */
         class FiltersCount extends SqlFunction {
                 
                 FiltersCount(DataSource ds) {
                         super(ds, "SELECT COUNT(*) from filters");
                         compile();
                 }                
         }
         
         /**
          * <code>Filter</code> Delete Object.
          */
         class FilterDelete extends SqlUpdate {
                 FilterDelete(DataSource ds, String s) {
                         super(ds, s);
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                 }
         }
         
         /**
          * <code>Filter</code> Query Object.
          * @author dick
          *
          */
         class FilterQuery extends MappingSqlQuery {
 
                 FilterQuery(DataSource ds) {
                         super(ds, "SELECT * from filters");
                         compile();
                         if (filterColumns == null) {
                                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                                 fcq.execute();
                         }
                 }
                 
                 FilterQuery(DataSource ds, String pic) {
                         super(ds, "SELECT * from filters where pic = ?");
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                         if (filterColumns == null) {
                                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                                 fcq.execute();
                         }
                 }
 
                 FilterQuery(DataSource ds, String s, int size) {
                         super(ds, "SELECT * from filters where CONCAT_WS(\"_\", ?, ?) != 0");
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                         if (filterColumns == null) {
                                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                                 fcq.execute();
                         }
                 }
                 
 /*                FilterQuery(DataSource ds, String s, int size, int rateOp, int rateVal) {
                         super(ds, "SELECT * from filters where CONCAT_WS(\"_\", ?, ?) ? ?");
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                         if (filterColumns == null) {
                                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                                 fcq.execute();
                         }
                 }
 */                
                 FilterQuery(DataSource ds, String s, int unused1, int unused2) {
 //                        super(ds, "SELECT * from filters where X_100 != 0 ORDER BY timestamp DESC LIMIT 1");
                         super(ds, s);
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                         if (filterColumns == null) {
                                 FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
                                 fcq.execute();
                         }
                 }
                 
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         Filter f = new Filter();
                         f.setPicName(rs.getString("pic"));
 //                        if (DEBUG)
 //                                logger.info("picname='" + f.getPicName() + "'");
                         try {
                                 f.getDate().setTime(rs.getTimestamp("date").getTime());
                         } catch (Exception e) {}
                         Iterator<String> i = filterColumns.iterator();
                         String        s;
                         while (i.hasNext()) {
                                 s = (String)i.next();
                                 if (s.equals("date"))
                                         continue;
                                 int        v = rs.getInt(s);
                                 //logger.info("k: '" + s + "' v:" + v);
                                 f.addFilter(s, v);
                         }
                         return(f);
                 }
         }
 
         /**
          * <code>Filter</code> Query Object.
          * @author dick
          *
          */
         class FilterSetQuery extends MappingSqlQuery {
 
                 FilterSetQuery(DataSource ds, String set) {
 //                        super(ds, "SELECT * from filters where (G_75) > 0 ");
                         super(ds, "SELECT * from filters where " + set + " > 0 ");
 //                        declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                         if (DEBUG)
                                 logger.info("FilterSetQuery()");
                 }
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         Filter f = new Filter();
                         f.setPicName(rs.getString("pic"));
                         //if (DEBUG)
                         //        logger.info("picname='" + f.getPicName() + "'");
                         try {
                                 f.getDate().setTime(rs.getTimestamp("date").getTime());
                         } catch (Exception e) {}
                         Iterator<String> i = filterColumns.iterator();
                         String        s;
                         while (i.hasNext()) {
                                 s = (String)i.next();
                                 if (s.equals("date"))
                                         continue;
                                 int        v = rs.getInt(s);
                                 //logger.info("k: '" + s + "' v:" + v);
                                 f.addFilter(s, v);
                         }
                         return(f);
                 }
         }
         /**
          * <code>Filter</code> Query Object.
          * @author dick
          *
          */
         class FilterSetNameQuery extends MappingSqlQuery {
 
                 FilterSetNameQuery(DataSource ds, String set) {
                         super(ds, "SELECT pic FROM filters WHERE " + set + " > 0");
                         compile();
                         if (DEBUG)
                                 logger.info("FilterSetQuery()");
                 }
                 FilterSetNameQuery(DataSource ds, String set, int rateOp, int rateVal) {
                         super(ds, "SELECT pic FROM filters WHERE " + set + " " + MetaSet.rateOps[rateOp] + " " + rateVal);
                         //declareParameter(new SqlParameter(Types.INTEGER));
                         //declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                         if (DEBUG)
                                 logger.info("FilterSetQuery()");
                 }
                 
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         if (DEBUG)
                                 logger.info("row=" + rowNum);
                         return(rs.getString("pic"));
                 }
         }
 
         /**
          * @author dick
          */
         class FilterColumnsQuery extends MappingSqlQuery {
                 FilterColumnsQuery(DataSource ds) {
                         super(ds, "SHOW COLUMNS from filters");
                         filterColumns = new LinkedList<String>();
                         compile();
                 }
                 
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         String s;
                         s = rs.getString("Field");
                         if (!s.equals("pic") && !s.equals("date"))
                                 filterColumns.add(s);
                         
                         return(s);
                 }
                 
         }
         
 }

1		/******************************************************************************
2		* FiltersDaoJdbc.java - Implement the Dao interface for the Filters
3		*
4		* PicMan - The BuckoSoft Picture Manager in Java
5		* Copyright(c) 2005 - Dick Balaska
6		*
7		*/
8		package com.buckosoft.PicMan.db;
9
10		import java.sql.ResultSet;
11		import java.sql.SQLException;
12		import java.sql.Types;
13		import java.util.Iterator;
14		import java.util.LinkedHashMap;
15		import java.util.LinkedList;
16		import java.util.List;
17
18		import javax.sql.DataSource;
19
20		import org.apache.commons.logging.Log;
21		import org.apache.commons.logging.LogFactory;
22		import org.springframework.jdbc.core.SqlParameter;
23		import org.springframework.jdbc.object.MappingSqlQuery;
24		import org.springframework.jdbc.object.SqlFunction;
25		import org.springframework.jdbc.object.SqlUpdate;
26
27		import com.buckosoft.PicMan.domain.Filter;
28		import com.buckosoft.PicMan.domain.MetaSet;
29		import com.buckosoft.PicMan.domain.Set;
30
31		/** Implement the Dao interface for the {@link com.buckosoft.PicMan.domain.Filter}s.
32		* @author Dick Balaska
33		* @since 2005/07/30
34		* @see com.buckosoft.PicMan.domain.Filter
35		* @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/FiltersDaoJdbc.java">FiltersDaoJdbc.java</a>
36		*/
37	0	public class FiltersDaoJdbc implements FiltersDao {
38
39		private final static boolean DEBUG = false;
40		private final static boolean DEBUGCONNECTIONLEAK = false;
41	0	protected final Log logger = LogFactory.getLog(getClass());
42
43		private LinkedList<String> filterColumns;
44
45		private DataSource ds;
46
47		/** Set the JDBC datasource reference
48		* @param ds The DataSource as generated by the Spring config/setup.
49		*/
50		public void setDataSource(DataSource ds) {
51	0	this.ds = ds;
52	0	}
53
54		/* (non-Javadoc)
55		* @see com.buckosoft.PicMan.db.FiltersDao#addSet(java.lang.String, int)
56		*/
57		public void addSet(String name, int size) {
58	0	String n = name;
59	0	if (size > 0)
60	0	n += "_" + size;
61	0	String s = "ALTER TABLE `filters` ADD `" + n + "` TINYINT DEFAULT '0' NOT NULL";
62		if (DEBUG)
63		logger.info(s);
64	0	SqlUpdate sf = new SqlUpdate(ds, s);
65	0	sf.update();
66	0	filterColumns = null;
67	0	}
68
69		public void renameSet(String oldName, String newName, int size) {
70	0	oldName += "_" + size;
71	0	newName += "_" + size;
72	0	String s = "ALTER TABLE `filters` CHANGE `" + oldName + "` `" + newName + "` TINYINT(4) NOT NULL DEFAULT '0'";
73		if (DEBUG)
74		logger.info(s);
75	0	SqlUpdate sf = new SqlUpdate(ds, s);
76	0	sf.update();
77	0	filterColumns = null;
78
79	0	}
80
81		/* (non-Javadoc)
82		* @see com.buckosoft.PicMan.db.FiltersDao#deleteSet(java.lang.String, int)
83		*/
84		public void deleteSet(String name, int size) {
85	0	String n = name;
86	0	if (size > 0)
87	0	n += "_" + size;
88	0	String s = "ALTER TABLE `filters` DROP `" + n + "`";
89		if (DEBUG)
90		logger.info(s);
91		try {
92	0	SqlUpdate sf = new SqlUpdate(ds, s);
93	0	sf.update();
94	0	} catch (Exception e) {
95	0	}
96	0	filterColumns = null;
97	0	}
98
99		/* (non-Javadoc)
100		* @see com.buckosoft.PicMan.db.FiltersDao#getFilters()
101		*/
102		@SuppressWarnings("unchecked")
103		public List<Filter> getFilters() {
104	0	if (filterColumns == null)
105	0	getFilterColumns();
106	0	FilterQuery fq = new FilterQuery(ds);
107	0	return(fq.execute());
108		}
109
110		/* (non-Javadoc)
111		* @see com.buckosoft.PicMan.db.FiltersDao#getFiltersBySet(java.lang.String, int)
112		*/
113		@SuppressWarnings("unchecked")
114		public List<Filter> getFiltersBySet(String setName, int size) {
115	0	if (filterColumns == null)
116	0	getFilterColumns();
117	0	String s = setName + "_" + size;
118	0	FilterSetQuery fq = new FilterSetQuery(ds, s);
119	0	return(fq.execute());
120		}
121
122		/* (non-Javadoc)
123		* @see com.buckosoft.PicMan.db.FiltersDao#getPicNamesBySet(java.lang.String, int)
124		*/
125		@SuppressWarnings("unchecked")
126		public List<String> getPicNamesBySet(Set set, int size) {
127	0	if (filterColumns == null)
128	0	getFilterColumns();
129	0	String s = "filters." + set.getName() + "_" + size;
130		if (DEBUG)
131		logger.info("getPicNamesBySet '" + s + "'");
132	0	if (sql_getPicNamesBySetQUERY == null)
133	0	sql_getPicNamesBySetQUERY = new FilterSetNameQuery(ds, s);
134	0	FilterSetNameQuery fq = new FilterSetNameQuery(ds, s);
135	0	return(fq.execute());
136		}
137		private FilterSetNameQuery sql_getPicNamesBySetQUERY;
138
139		/* (non-Javadoc)
140		* @see com.buckosoft.PicMan.db.FiltersDao#getPicNamesBySet(java.lang.String, int, int, int)
141		*/
142		@SuppressWarnings("unchecked")
143		public List<String> getPicNamesBySet(Set set, int size, int rateOp, int rateVal) {
144	0	if (rateOp == 0)
145	0	return(getPicNamesBySet(set, size));
146	0	if (filterColumns == null)
147	0	getFilterColumns();
148	0	String s = "filters." + set.getName() + "_" + size;
149		if (DEBUG)
150		logger.info("getPicNamesBySet '" + s + "'");
151	0	if (sql_getPicNamesBySetRVQUERY == null)
152	0	sql_getPicNamesBySetRVQUERY = new FilterSetNameQuery(ds, s, rateOp, rateVal);
153	0	FilterSetNameQuery fq = new FilterSetNameQuery(ds, s, rateOp, rateVal);
154		//return(fq.execute(new Object[] {MetaSet.rateOps[rateOp], new Integer(rateVal)}));
155	0	return(fq.execute());
156		}
157		private FilterSetNameQuery sql_getPicNamesBySetRVQUERY;
158
159		/* (non-Javadoc)
160		* @see com.buckosoft.PicMan.db.FiltersDao#getFilter(java.lang.String)
161		*/
162		public Filter getFilter(String picName) {
163	0	if (filterColumns == null)
164	0	getFilterColumns();
165		if (DEBUG)
166		logger.info("getFilter: " + picName);
167	0	if (sql_getFilterSELECTr == null)
168	0	sql_getFilterSELECTr = new FilterQuery(ds, picName);
169	0	Filter f = (Filter)sql_getFilterSELECTr.findObject(picName);
170	0	if (f != null)
171	0	return(f);
172	0	f = new Filter();
173	0	logger.warn("Creating new filter for pic " + picName);
174	0	f.setPicName(picName);
175	0	Iterator<String> i = filterColumns.iterator();
176		String s;
177	0	while (i.hasNext()) {
178	0	s = (String)i.next();
179	0	if (s.equals("date"))
180	0	continue;
181	0	f.addFilter(s, 0);
182		}
183	0	return(f);
184		}
185		private FilterQuery sql_getFilterSELECTr;
186
187		/* (non-Javadoc)
188		* @see com.buckosoft.PicMan.db.FiltersDao#getFilterColumns()
189		*/
190		public List<String> getFilterColumns() {
191	0	if (filterColumns != null)
192	0	return(filterColumns);
193		if (DEBUG)
194		logger.info("getFilterColumns()");
195	0	FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
196	0	fcq.execute();
197	0	return(filterColumns); // ugly
198		}
199
200		/* (non-Javadoc)
201		* @see com.buckosoft.PicMan.db.FiltersDao#addFilter(com.buckosoft.PicMan.domain.Filter)
202		*/
203		public void addFilter(Filter filter) {
204		String s;
205	0	if (filterColumns == null)
206	0	getFilterColumns();
207	0	if (sql_addFilterDELETEr == null)
208	0	sql_addFilterDELETEr = new FilterDelete(ds, "DELETE FROM filters WHERE pic = ?");
209
210	0	s = "DELETE FROM filters WHERE pic = \"" + filter.getPicName() + "\"";
211		if (DEBUG)
212		logger.info(s);
213	0	SqlUpdate sf = new SqlUpdate(ds, s);
214	0	sf.update();
215
216		String key;
217	0	s = "`pic`,`date`";
218	0	String t = "'" + filter.getPicName() + "','" + new java.sql.Timestamp(filter.getDate().getTime()).toString() + "'" ;
219	0	LinkedHashMap<String, Integer> map = filter.getFilters();
220	0	Iterator<String> it = map.keySet().iterator();
221	0	while (it.hasNext()) {
222	0	key = (String)it.next();
223	0	if (key.indexOf('_') == -1) // don't include microSets in this sql update
224	0	continue;
225	0	s += ",`" + key + "`";
226	0	t += ",'" + map.get(key) + "'";
227		}
228		if (DEBUG)
229		logger.info("s=" + s + " t=" + t);
230		SqlUpdate su;
231	0	su = new SqlUpdate(ds, "INSERT INTO filters (" + s + ") VALUES(" + t + ")");
232	0	su.update();
233	0	}
234		private SqlUpdate sql_addFilterDELETEr;
235
236		/* (non-Javadoc)
237		* @see com.buckosoft.PicMan.db.FiltersDao#getFilterCount()
238		*/
239		public int getFilterCount() {
240		if (DEBUGCONNECTIONLEAK)
241		return(269);
242	0	if (sql_getFilterCountCOUNTr == null)
243	0	sql_getFilterCountCOUNTr = new FiltersCount(ds);
244		if (DEBUG)
245		logger.info("getFilterCount:");
246	0	return(sql_getFilterCountCOUNTr.run());
247		}
248		private FiltersCount sql_getFilterCountCOUNTr;
249
250		/**
251		* <code>Filter</code> Count Object.
252		*/
253		class FiltersCount extends SqlFunction {
254
255	0	FiltersCount(DataSource ds) {
256	0	super(ds, "SELECT COUNT(*) from filters");
257	0	compile();
258	0	}
259		}
260
261		/**
262		* <code>Filter</code> Delete Object.
263		*/
264		class FilterDelete extends SqlUpdate {
265	0	FilterDelete(DataSource ds, String s) {
266	0	super(ds, s);
267	0	declareParameter(new SqlParameter(Types.VARCHAR));
268	0	compile();
269	0	}
270		}
271
272		/**
273		* <code>Filter</code> Query Object.
274		* @author dick
275		*
276		*/
277		class FilterQuery extends MappingSqlQuery {
278
279	0	FilterQuery(DataSource ds) {
280	0	super(ds, "SELECT * from filters");
281	0	compile();
282	0	if (filterColumns == null) {
283	0	FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
284	0	fcq.execute();
285		}
286	0	}
287
288	0	FilterQuery(DataSource ds, String pic) {
289	0	super(ds, "SELECT * from filters where pic = ?");
290	0	declareParameter(new SqlParameter(Types.VARCHAR));
291	0	compile();
292	0	if (filterColumns == null) {
293	0	FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
294	0	fcq.execute();
295		}
296	0	}
297
298	0	FilterQuery(DataSource ds, String s, int size) {
299	0	super(ds, "SELECT * from filters where CONCAT_WS(\"_\", ?, ?) != 0");
300	0	declareParameter(new SqlParameter(Types.VARCHAR));
301	0	declareParameter(new SqlParameter(Types.INTEGER));
302	0	compile();
303	0	if (filterColumns == null) {
304	0	FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
305	0	fcq.execute();
306		}
307	0	}
308
309		/* FilterQuery(DataSource ds, String s, int size, int rateOp, int rateVal) {
310		super(ds, "SELECT * from filters where CONCAT_WS(\"_\", ?, ?) ? ?");
311		declareParameter(new SqlParameter(Types.VARCHAR));
312		declareParameter(new SqlParameter(Types.INTEGER));
313		declareParameter(new SqlParameter(Types.INTEGER));
314		declareParameter(new SqlParameter(Types.INTEGER));
315		compile();
316		if (filterColumns == null) {
317		FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
318		fcq.execute();
319		}
320		}
321		*/
322	0	FilterQuery(DataSource ds, String s, int unused1, int unused2) {
323		// super(ds, "SELECT * from filters where X_100 != 0 ORDER BY timestamp DESC LIMIT 1");
324	0	super(ds, s);
325	0	declareParameter(new SqlParameter(Types.VARCHAR));
326	0	compile();
327	0	if (filterColumns == null) {
328	0	FilterColumnsQuery fcq = new FilterColumnsQuery(ds);
329	0	fcq.execute();
330		}
331	0	}
332
333		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
334	0	Filter f = new Filter();
335	0	f.setPicName(rs.getString("pic"));
336		// if (DEBUG)
337		// logger.info("picname='" + f.getPicName() + "'");
338		try {
339	0	f.getDate().setTime(rs.getTimestamp("date").getTime());
340	0	} catch (Exception e) {}
341	0	Iterator<String> i = filterColumns.iterator();
342		String s;
343	0	while (i.hasNext()) {
344	0	s = (String)i.next();
345	0	if (s.equals("date"))
346	0	continue;
347	0	int v = rs.getInt(s);
348		//logger.info("k: '" + s + "' v:" + v);
349	0	f.addFilter(s, v);
350	0	}
351	0	return(f);
352		}
353		}
354
355		/**
356		* <code>Filter</code> Query Object.
357		* @author dick
358		*
359		*/
360		class FilterSetQuery extends MappingSqlQuery {
361
362	0	FilterSetQuery(DataSource ds, String set) {
363		// super(ds, "SELECT * from filters where (G_75) > 0 ");
364	0	super(ds, "SELECT * from filters where " + set + " > 0 ");
365		// declareParameter(new SqlParameter(Types.VARCHAR));
366	0	compile();
367		if (DEBUG)
368		logger.info("FilterSetQuery()");
369	0	}
370		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
371	0	Filter f = new Filter();
372	0	f.setPicName(rs.getString("pic"));
373		//if (DEBUG)
374		// logger.info("picname='" + f.getPicName() + "'");
375		try {
376	0	f.getDate().setTime(rs.getTimestamp("date").getTime());
377	0	} catch (Exception e) {}
378	0	Iterator<String> i = filterColumns.iterator();
379		String s;
380	0	while (i.hasNext()) {
381	0	s = (String)i.next();
382	0	if (s.equals("date"))
383	0	continue;
384	0	int v = rs.getInt(s);
385		//logger.info("k: '" + s + "' v:" + v);
386	0	f.addFilter(s, v);
387	0	}
388	0	return(f);
389		}
390		}
391		/**
392		* <code>Filter</code> Query Object.
393		* @author dick
394		*
395		*/
396		class FilterSetNameQuery extends MappingSqlQuery {
397
398	0	FilterSetNameQuery(DataSource ds, String set) {
399	0	super(ds, "SELECT pic FROM filters WHERE " + set + " > 0");
400	0	compile();
401		if (DEBUG)
402		logger.info("FilterSetQuery()");
403	0	}
404	0	FilterSetNameQuery(DataSource ds, String set, int rateOp, int rateVal) {
405	0	super(ds, "SELECT pic FROM filters WHERE " + set + " " + MetaSet.rateOps[rateOp] + " " + rateVal);
406		//declareParameter(new SqlParameter(Types.INTEGER));
407		//declareParameter(new SqlParameter(Types.INTEGER));
408	0	compile();
409		if (DEBUG)
410		logger.info("FilterSetQuery()");
411	0	}
412
413		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
414		if (DEBUG)
415		logger.info("row=" + rowNum);
416	0	return(rs.getString("pic"));
417		}
418		}
419
420		/**
421		* @author dick
422		*/
423		class FilterColumnsQuery extends MappingSqlQuery {
424	0	FilterColumnsQuery(DataSource ds) {
425	0	super(ds, "SHOW COLUMNS from filters");
426	0	filterColumns = new LinkedList<String>();
427	0	compile();
428	0	}
429
430		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
431		String s;
432	0	s = rs.getString("Field");
433	0	if (!s.equals("pic") && !s.equals("date"))
434	0	filterColumns.add(s);
435
436	0	return(s);
437		}
438
439		}
440
441		}