Coverage Report

 /******************************************************************************
  * PicsDaoJdbc.java - Implement the Dao interface for the Pics
  * 
  * 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.Calendar;
 import java.util.Date;
 import java.util.HashMap;
 import java.util.Iterator;
 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.dao.DataAccessException;
 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.Pic;
 
 /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Pic}s.
  * @author Dick Balaska
  * @since 2005/07/31
  * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/PicsDaoJdbc.java">PicsDaoJdbc.java</a>
  */
 public class PicsDaoJdbc implements PicsDao {
 
         private final static boolean DEBUG = false;
         private final Log logger = LogFactory.getLog(getClass());
         
         private DataSource ds;
         
         /** Set the reference to the JDBC datasource.
          * @param ds The datasource as configured by Spring.
          * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>
          */ 
         public void setDataSource(DataSource ds) {
                 this.ds = ds;
         }
         
         /** Get a List of all the pics in the database
          * @return The (probably big) List
          */
         @SuppressWarnings("unchecked")
         public List<Pic> getPics() {
                 if (DEBUG)
                         logger.info("GetPicsList");
 //                PicsQuery pq = new PicsQuery(ds);
 //                return pq.execute();
                 if (sql_getPicsListQUERY == null)
                         sql_getPicsListQUERY = new PicsQuery(ds);
                 return(sql_getPicsListQUERY.execute());
         }
         private        PicsQuery        sql_getPicsListQUERY = null;
 
         /** Get a single named Pic
          * @param name The name of the pic to fetch
          */
         public Pic getPic(String name) {
 //                if (DEBUG)
 //                        logger.info("getPic: " + name);
                 if (sql_getPicQUERY == null)
                         sql_getPicQUERY = new PicsQuery(ds, name);
         return (Pic)sql_getPicQUERY.findObject(name);
         }
         private        PicsQuery        sql_getPicQUERY = null;
 
         /** Get a single Pic
          * @param pid The pid of the pic to fetch
          */
         public Pic getPic(int pid) {
 //                if (DEBUG)
 //                        logger.info("getPic: " + pid);
                 if (sql_getPicPidQUERY == null)
                         sql_getPicPidQUERY = new PicsQuery(ds, pid);
         return (Pic)sql_getPicPidQUERY.findObject(pid);
         }
         private        PicsQuery        sql_getPicPidQUERY = null;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getNewestPic()
          */
         public Pic getNewestPic() {
                 if (DEBUG)
                         logger.info("getNewestPic:");
                 if (sql_getPicNewQUERY == null)
                         sql_getPicNewQUERY = new PicsQuery(ds, ds);
         return (Pic)sql_getPicNewQUERY.execute().get(0);
         }
         private        PicsQuery        sql_getPicNewQUERY = null;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getRandomPic()
          */
         public Pic getRandomPic() {
                 int max = this.getPicCount();
                 long r = Math.round(Math.random()*max);
                 return(getPic((int)r));
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getRandomPic(int)
          */
         public Pic getRandomPic(int rid) {
                 int max = this.getPicCount(rid);
                 long r = Math.round(Math.random()*max);
                 if (sql_getPicRandQUERY == null)
                         sql_getPicRandQUERY = new PicsQuery(ds, rid, r);
                 Pic p = (Pic)sql_getPicRandQUERY.findObject(rid, (int)r);
                 if (DEBUG)
                         logger.info("getRandomPic: " + rid + " " + r + " = " + p.getName());
         return(p);
         }
         private        PicsQuery        sql_getPicRandQUERY = null;
 
 
         /** Get a list of Pics, wildcards supported
          * @param name The name of the Pic to match (user must supply sql '%' for wildcards)
          * @return The List
          */
         @SuppressWarnings("unchecked")
         public List<Pic> getPics(String name) {
                 if (DEBUG)
                         logger.info("getPics: " + name);
                 if (sql_getPicsQUERY == null)
                         sql_getPicsQUERY = new PicsQuery(ds, name, true);
         return (sql_getPicsQUERY.execute(name));
         }
         private        PicsQuery        sql_getPicsQUERY = null;
 
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicsByMD5Sum(long)
          */
         @Override
         @SuppressWarnings("unchecked")
         public List<Pic> getPicsByMD5Sum(long md5sum) {
                 if (sql_getPicsMD5QUERY == null)
                         sql_getPicsMD5QUERY = new PicMD5SumQuery(ds, 0);
                 return(sql_getPicsMD5QUERY.execute(md5sum));
         }
         private        PicMD5SumQuery        sql_getPicsMD5QUERY = null;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPics(java.util.List)
          */
         public List<Pic> getPics(List<String> list) {
                 LinkedList<Pic> picList = new LinkedList<Pic>();
                 Iterator<String> iter = list.iterator();
                 while (iter.hasNext()) {
                         String s = iter.next();
                         picList.add(getPic(s));
                 }
                 return(picList);
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicsInDir(int rid, java.lang.String)
          */
         @SuppressWarnings("unchecked")
         public List<Pic> getPicsInDir(int rid, String dirName) {
                 if (DEBUG)
                         logger.info("getPics: " + dirName);
                 if (sql_getPicsDirQUERY == null)
                         sql_getPicsDirQUERY = new PicsQuery(ds, dirName, rid);
         return (sql_getPicsDirQUERY.execute(new Object[] {dirName, new Integer(rid)}));
         }
         private        PicsQuery        sql_getPicsDirQUERY = null;
 
         /*
          * (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicsNewerThan(java.util.Calendar)
          */
         @SuppressWarnings("unchecked")
         public List<Pic> getPicsNewerThan(Calendar calendar) {
                 if (DEBUG)
                         logger.info("getPicsNewerThan: " + calendar.getTime().toString());
                 if (sql_getPicsNewerQUERY == null)
                         sql_getPicsNewerQUERY = new PicsQuery(ds, calendar);
         return (sql_getPicsNewerQUERY.execute(new Object[] {calendar.getTime()}));
         }
         private        PicsQuery        sql_getPicsNewerQUERY = null;
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicsMap()
          */
         public HashMap<String, Date> getPicsMap() {
                 if (DEBUG)
                         logger.info("GetPicsMap");
                 List<Pic> l = getPics();
                 HashMap<String, Date> hm = new HashMap<String, Date>();
                 Iterator<Pic> i = l.iterator();
                 Pic p;
                 while (i.hasNext()) {
                         p = (Pic)i.next();
                         hm.put(p.getName(), p.getDate());        // need something for the value, might as well use timestamp
                 }
                 return(hm);
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicNames()
          */
         @SuppressWarnings("unchecked")
         public List<String> getPicNames() {
                 PicNameQuery pnq = new PicNameQuery(ds);
                 return(pnq.execute());
         }
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByDateFunc(java.lang.String, java.lang.String)
          */
         @SuppressWarnings("unchecked")
         public List<String> getPicNamesByDateFunc(String operator, String operand) {
                 PicNameQuery pnq = new PicNameQuery(ds, "DATE(`timestamp`)", operator, operand);
                 return(pnq.execute());
         }
 
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByRootFunc(java.lang.String, int)
          */
         @SuppressWarnings("unchecked")
         public List<String> getPicNamesByRootFunc(String operator, int rid) {
                 PicNameQuery pnq = new PicNameQuery(ds, "rid", operator, rid);
                 return(pnq.execute());
         }
 
 /*        @SuppressWarnings("unchecked")
         public List<String> getPicNamesInSet(Set set) {
                 if (DEBUG)
                         logger.info("Getting pics from set \"" + set.getName() + "\"!");
                 PicsQuery pq = new PicsQuery(ds, set);
                 return(pq.execute());           
         }
 */
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#addPic(com.buckosoft.PicMan.domain.Pic)
          */
         public void        addPic(Pic pic) {
                 if (DEBUG)
                         logger.info("addPic: " + pic.getName());
                 PicInsert pi = new PicInsert(ds);
                 pi.insert(pic);
                 Pic pic1 = getPic(pic.getName());
                 pic.setPid(pic1.getPid());
                 PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
                 psu.update(pic);
         }
 
 
         /** Update this <code>Pic</code> in the database
          * @param pic The Pic to store
          */
         public void updatePic(Pic pic) throws DataAccessException {
                 PicUpdate pu = new PicUpdate(ds);
                 pu.update(pic);        
                 PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
                 psu.update(pic);
         }
         
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getMaxThumbCacheDirUsed()
          */
         @SuppressWarnings("unchecked")
         public int getMaxThumbCacheDirUsed() {
                 PicsQuery pq = new PicsQuery(ds, false);
                 List<Pic> list = pq.execute();
                 if (list.size() == 0)
                         return(0);
                 Pic pic = list.get(0);
                 if (pic == null)
                         return(0);
                 return(pic.getCacheDir());
         }
 
         public int getPicCount() {
                 PicCount pc = new PicCount(ds);
                 return(pc.run());
                 
         }
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getPicCount(int)
          */
         public int getPicCount(int rid) {
                 PicCount pc = new PicCount(ds, rid, false);
                 return(pc.run(rid));
         }
 
 
         /* (non-Javadoc)
          * @see com.buckosoft.PicMan.db.PicsDao#getThumbCacheFillCount(int)
          */
         public int getThumbCacheFillCount(int cacheDir) {
                 PicCount pc = new PicCount(ds, cacheDir);
                 return(pc.run(cacheDir));
         }
 
         ///////////////////////////////////////////////////////////////////////////
         /**
          * Object to Query for Pics
          */
         private class PicsQuery extends MappingSqlQuery {
                 
                 PicsQuery(DataSource ds) {
                         super(ds, "SELECT * from pics");
                         compile();
                 }
                 
                 PicsQuery(DataSource ds, String name) {
                         super(ds, "SELECT * from pics where name = ?");
             declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                 }
                 PicsQuery(DataSource ds, int pid) {
                         super(ds, "SELECT * from pics where pid = ?");
             declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                 }
                 PicsQuery(DataSource ds, String name, boolean unused) {
                         super(ds, "SELECT * from pics where name like ?");
             declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                 }
 /*                PicsQuery(DataSource ds, Set set) {
                         super(ds, "SELECT * from pics where " + set.getName() + " != 0");
                         compile();
                 }
 */
                 PicsQuery(DataSource ds, Calendar calendar) {
                         super(ds, "SELECT * from pics where timestamp > ?");
                         declareParameter(new SqlParameter(Types.TIMESTAMP));
                         compile();
                 }
                 PicsQuery(DataSource ds, String dirName, int rid) {
                         super(ds, "SELECT * from pics where location = ? AND rid = ? ORDER BY name");
             declareParameter(new SqlParameter(Types.VARCHAR));
             declareParameter(new SqlParameter(Types.INTEGER));
                         compile();                        
                 }
                 PicsQuery(DataSource ds, DataSource unused) {
                         super(ds, "SELECT * from pics ORDER BY timestamp DESC LIMIT 1");
                         compile();                        
                 }
 
 /*                PicsQuery(DataSource ds, DataSource unused, boolean unused1) {
                         super(ds, "SELECT * from pics ORDER BY RAND() LIMIT 1");
                         compile();                        
                 }
 */
                 PicsQuery(DataSource ds, int rid, long r) {
                         super(ds, "SELECT * from pics where rid = ? LIMIT ?, 1");
             declareParameter(new SqlParameter(Types.INTEGER));
             declareParameter(new SqlParameter(Types.INTEGER));
                         compile();                        
                 }
 
                 PicsQuery(DataSource ds, boolean unused) {
                         super(ds, "SELECT * from pics ORDER BY cacheDir DESC LIMIT 1");
                         compile();                        
                 }
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         Pic pic = new Pic();
                         pic.setName(rs.getString("name"));
                         if (false)
                                 logger.info("picName=" + pic.getName());
                         try {
                                 pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
                         } catch (Exception e) {}
                         pic.setPid(rs.getInt("pid"));
                         pic.setWidth(rs.getInt("width"));
                         pic.setHeight(rs.getInt("height"));
                         pic.setRid(rs.getInt("rid"));
                         pic.setCacheDir(rs.getInt("cacheDir"));
                         pic.setLocation(rs.getString("location"));
                         return pic;
                 }
         }
 
         private class PicMD5SumQuery extends MappingSqlQuery {
                 PicMD5SumQuery(DataSource ds, long sum) {
                         super(ds, "SELECT * FROM pics p left join picmd5sums s on p.pid = s.pid WHERE s.md5sum = ?");
             declareParameter(new SqlParameter(Types.BIGINT));
                         compile();                        
                 }
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         Pic pic = new Pic();
                         pic.setName(rs.getString("name"));
                         if (false)
                                 logger.info("picName=" + pic.getName());
                         try {
                                 pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
                         } catch (Exception e) {}
                         pic.setPid(rs.getInt("pid"));
                         pic.setWidth(rs.getInt("width"));
                         pic.setHeight(rs.getInt("height"));
                         pic.setRid(rs.getInt("rid"));
                         pic.setCacheDir(rs.getInt("cacheDir"));
                         pic.setLocation(rs.getString("location"));
                         pic.setMd5(rs.getLong("md5sum"));
                         return pic;
                 }
 
         }
 
         /**
          * <code>Pic</code> Query Object for just the name.
          * @author dick
          *
          */
         private class PicNameQuery extends MappingSqlQuery {
 
                 PicNameQuery(DataSource ds) {
                         super(ds, "SELECT name FROM pics");
                         compile();
 
                 }
                 PicNameQuery(DataSource ds, String column, String operator, String operand) {
                         super(ds, "SELECT name FROM pics WHERE " +  column + " " + operator + " \"" + operand + "\"");
                         compile();
                         if (DEBUG)
                                 logger.info("PicNameQuery() WHERE " +  column + " " + operator + " \"" + operand + "\"");
 
                 }
                 PicNameQuery(DataSource ds, String column, String operator, int operand) {
                         super(ds, "SELECT name FROM pics WHERE " +  column + " " + operator + " \"" + operand + "\"");
                         compile();
                         if (DEBUG)
                                 logger.info("PicNameQuery() WHERE " +  column + " " + operator + " \"" + operand + "\"");
 
                 }
                 
                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
 //                        if (DEBUG)
 //                                logger.info("row=" + rowNum);
                         return(rs.getString("name"));
                 }
         }
         
         /**
          * <code>Pic</code> Count Object.
          */
         private class PicCount extends SqlFunction {
                 
                 PicCount(DataSource ds) {
                         super(ds, "SELECT COUNT(*) from pics");
                         compile();
                 }
                 /** Count the entries in a cacheDir
                  * @param ds
                  * @param cacheDir
                  */
                 PicCount(DataSource ds, int cacheDir) {
                         super(ds, "SELECT COUNT(*) from pics WHERE cacheDir = ?");
                         declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                 }                
                 /** Count the entries in a root
                  * @param ds
                  * @param rid The Root id to query
                  * @param unused a boolean
                  */
                 PicCount(DataSource ds, int rid, boolean unused) {
                         super(ds, "SELECT COUNT(*) from pics WHERE rid = ?");
                         declareParameter(new SqlParameter(Types.INTEGER));
                         compile();
                 }                
         }
 
         /**
          * <code>Pic</code> Insert Object.
          */
         private class PicInsert extends SqlUpdate {
                 
                 /**
                  * Create a new instance of PicInsert.
                  * @param ds the DataSource to use for the insert
                  */
                 protected PicInsert(DataSource ds) {
                         super(ds, "INSERT INTO pics VALUES(?,?,?,?,?,?,?,?)");
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.TIMESTAMP));
                         compile();
                 }
 
                 protected void insert(Pic p) {
                         Object[] objs = new Object[] {
                                         new Integer(0),
                                         p.getName(), new Integer(p.getWidth()), new Integer(p.getHeight()), 
                                         new Integer(p.getRid()), p.getLocation(),
                                         new Integer(p.getCacheDir()),
                                         p.getDate()
                                 };
                         super.update(objs);
                 }
         }
         ///////////////////////////////////////////////////////////////////////////////////////////////
         /**
          * <code>Pic</code> Update Object.
          */
         private class PicUpdate extends SqlUpdate {
                 //private        DataSource        ds;
                 
                 /**
                  * Create a new instance of PicUpdate.
                  * @param ds the DataSource to use for the update
                  */
                 protected PicUpdate(DataSource ds) {
                         super(ds, "UPDATE pics SET width=?, height=?, rid=?, location=?, cacheDir=?, timestamp=? WHERE name = ? LIMIT 1");
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.TIMESTAMP));
                         declareParameter(new SqlParameter(Types.VARCHAR));
                         compile();
                 }
                 /**
                  * Method to update the <code>Set</code>'s data.
                  * @param List sets The sets that should be stored
                  * @return 0
                  */
 
                 protected int update(Pic pic) {
                         return this.update(new Object[] {
                                         new Integer(pic.getWidth()),
                                         new Integer(pic.getHeight()),
                                         new Integer(pic.getRid()),
                                         pic.getLocation(),
                                         new Integer(pic.getCacheDir()),
                                         pic.getDate(),
                                         pic.getName(),
                         });
                 }
         }
         
         private        class PicMD5SumUpdate extends SqlUpdate {
                 protected PicMD5SumUpdate(DataSource ds) {
                         super(ds, "REPLACE INTO picmd5sums VALUES(?,?) ");
                         declareParameter(new SqlParameter(Types.INTEGER));
                         declareParameter(new SqlParameter(Types.BIGINT));
                 }
                 protected void update(Pic p) {
                         Object[] objs = new Object[] {
                                         new Integer(p.getPid()),
                                         new Long(p.getMd5())
                                         };
                         this.update(objs);
                 }
         }
         
 }

1		/******************************************************************************
2		* PicsDaoJdbc.java - Implement the Dao interface for the Pics
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.Calendar;
14		import java.util.Date;
15		import java.util.HashMap;
16		import java.util.Iterator;
17		import java.util.LinkedList;
18		import java.util.List;
19
20		import javax.sql.DataSource;
21
22		import org.apache.commons.logging.Log;
23		import org.apache.commons.logging.LogFactory;
24		import org.springframework.dao.DataAccessException;
25		import org.springframework.jdbc.core.SqlParameter;
26		import org.springframework.jdbc.object.MappingSqlQuery;
27		import org.springframework.jdbc.object.SqlFunction;
28		import org.springframework.jdbc.object.SqlUpdate;
29
30		import com.buckosoft.PicMan.domain.Pic;
31
32		/** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Pic}s.
33		* @author Dick Balaska
34		* @since 2005/07/31
35		* @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/PicsDaoJdbc.java">PicsDaoJdbc.java</a>
36		*/
37	0	public class PicsDaoJdbc implements PicsDao {
38
39		private final static boolean DEBUG = false;
40	0	private final Log logger = LogFactory.getLog(getClass());
41
42		private DataSource ds;
43
44		/** Set the reference to the JDBC datasource.
45		* @param ds The datasource as configured by Spring.
46		* @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>
47		*/
48		public void setDataSource(DataSource ds) {
49	0	this.ds = ds;
50	0	}
51
52		/** Get a List of all the pics in the database
53		* @return The (probably big) List
54		*/
55		@SuppressWarnings("unchecked")
56		public List<Pic> getPics() {
57		if (DEBUG)
58		logger.info("GetPicsList");
59		// PicsQuery pq = new PicsQuery(ds);
60		// return pq.execute();
61	0	if (sql_getPicsListQUERY == null)
62	0	sql_getPicsListQUERY = new PicsQuery(ds);
63	0	return(sql_getPicsListQUERY.execute());
64		}
65	0	private PicsQuery sql_getPicsListQUERY = null;
66
67		/** Get a single named Pic
68		* @param name The name of the pic to fetch
69		*/
70		public Pic getPic(String name) {
71		// if (DEBUG)
72		// logger.info("getPic: " + name);
73	0	if (sql_getPicQUERY == null)
74	0	sql_getPicQUERY = new PicsQuery(ds, name);
75	0	return (Pic)sql_getPicQUERY.findObject(name);
76		}
77	0	private PicsQuery sql_getPicQUERY = null;
78
79		/** Get a single Pic
80		* @param pid The pid of the pic to fetch
81		*/
82		public Pic getPic(int pid) {
83		// if (DEBUG)
84		// logger.info("getPic: " + pid);
85	0	if (sql_getPicPidQUERY == null)
86	0	sql_getPicPidQUERY = new PicsQuery(ds, pid);
87	0	return (Pic)sql_getPicPidQUERY.findObject(pid);
88		}
89	0	private PicsQuery sql_getPicPidQUERY = null;
90
91		/* (non-Javadoc)
92		* @see com.buckosoft.PicMan.db.PicsDao#getNewestPic()
93		*/
94		public Pic getNewestPic() {
95		if (DEBUG)
96		logger.info("getNewestPic:");
97	0	if (sql_getPicNewQUERY == null)
98	0	sql_getPicNewQUERY = new PicsQuery(ds, ds);
99	0	return (Pic)sql_getPicNewQUERY.execute().get(0);
100		}
101	0	private PicsQuery sql_getPicNewQUERY = null;
102
103		/* (non-Javadoc)
104		* @see com.buckosoft.PicMan.db.PicsDao#getRandomPic()
105		*/
106		public Pic getRandomPic() {
107	0	int max = this.getPicCount();
108	0	long r = Math.round(Math.random()*max);
109	0	return(getPic((int)r));
110		}
111
112		/* (non-Javadoc)
113		* @see com.buckosoft.PicMan.db.PicsDao#getRandomPic(int)
114		*/
115		public Pic getRandomPic(int rid) {
116	0	int max = this.getPicCount(rid);
117	0	long r = Math.round(Math.random()*max);
118	0	if (sql_getPicRandQUERY == null)
119	0	sql_getPicRandQUERY = new PicsQuery(ds, rid, r);
120	0	Pic p = (Pic)sql_getPicRandQUERY.findObject(rid, (int)r);
121		if (DEBUG)
122		logger.info("getRandomPic: " + rid + " " + r + " = " + p.getName());
123	0	return(p);
124		}
125	0	private PicsQuery sql_getPicRandQUERY = null;
126
127
128		/** Get a list of Pics, wildcards supported
129		* @param name The name of the Pic to match (user must supply sql '%' for wildcards)
130		* @return The List
131		*/
132		@SuppressWarnings("unchecked")
133		public List<Pic> getPics(String name) {
134		if (DEBUG)
135		logger.info("getPics: " + name);
136	0	if (sql_getPicsQUERY == null)
137	0	sql_getPicsQUERY = new PicsQuery(ds, name, true);
138	0	return (sql_getPicsQUERY.execute(name));
139		}
140	0	private PicsQuery sql_getPicsQUERY = null;
141
142
143		/* (non-Javadoc)
144		* @see com.buckosoft.PicMan.db.PicsDao#getPicsByMD5Sum(long)
145		*/
146		@Override
147		@SuppressWarnings("unchecked")
148		public List<Pic> getPicsByMD5Sum(long md5sum) {
149	0	if (sql_getPicsMD5QUERY == null)
150	0	sql_getPicsMD5QUERY = new PicMD5SumQuery(ds, 0);
151	0	return(sql_getPicsMD5QUERY.execute(md5sum));
152		}
153	0	private PicMD5SumQuery sql_getPicsMD5QUERY = null;
154
155		/* (non-Javadoc)
156		* @see com.buckosoft.PicMan.db.PicsDao#getPics(java.util.List)
157		*/
158		public List<Pic> getPics(List<String> list) {
159	0	LinkedList<Pic> picList = new LinkedList<Pic>();
160	0	Iterator<String> iter = list.iterator();
161	0	while (iter.hasNext()) {
162	0	String s = iter.next();
163	0	picList.add(getPic(s));
164	0	}
165	0	return(picList);
166		}
167
168		/* (non-Javadoc)
169		* @see com.buckosoft.PicMan.db.PicsDao#getPicsInDir(int rid, java.lang.String)
170		*/
171		@SuppressWarnings("unchecked")
172		public List<Pic> getPicsInDir(int rid, String dirName) {
173		if (DEBUG)
174		logger.info("getPics: " + dirName);
175	0	if (sql_getPicsDirQUERY == null)
176	0	sql_getPicsDirQUERY = new PicsQuery(ds, dirName, rid);
177	0	return (sql_getPicsDirQUERY.execute(new Object[] {dirName, new Integer(rid)}));
178		}
179	0	private PicsQuery sql_getPicsDirQUERY = null;
180
181		/*
182		* (non-Javadoc)
183		* @see com.buckosoft.PicMan.db.PicsDao#getPicsNewerThan(java.util.Calendar)
184		*/
185		@SuppressWarnings("unchecked")
186		public List<Pic> getPicsNewerThan(Calendar calendar) {
187		if (DEBUG)
188		logger.info("getPicsNewerThan: " + calendar.getTime().toString());
189	0	if (sql_getPicsNewerQUERY == null)
190	0	sql_getPicsNewerQUERY = new PicsQuery(ds, calendar);
191	0	return (sql_getPicsNewerQUERY.execute(new Object[] {calendar.getTime()}));
192		}
193	0	private PicsQuery sql_getPicsNewerQUERY = null;
194
195		/* (non-Javadoc)
196		* @see com.buckosoft.PicMan.db.PicsDao#getPicsMap()
197		*/
198		public HashMap<String, Date> getPicsMap() {
199		if (DEBUG)
200		logger.info("GetPicsMap");
201	0	List<Pic> l = getPics();
202	0	HashMap<String, Date> hm = new HashMap<String, Date>();
203	0	Iterator<Pic> i = l.iterator();
204		Pic p;
205	0	while (i.hasNext()) {
206	0	p = (Pic)i.next();
207	0	hm.put(p.getName(), p.getDate()); // need something for the value, might as well use timestamp
208		}
209	0	return(hm);
210		}
211
212		/* (non-Javadoc)
213		* @see com.buckosoft.PicMan.db.PicsDao#getPicNames()
214		*/
215		@SuppressWarnings("unchecked")
216		public List<String> getPicNames() {
217	0	PicNameQuery pnq = new PicNameQuery(ds);
218	0	return(pnq.execute());
219		}
220
221		/* (non-Javadoc)
222		* @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByDateFunc(java.lang.String, java.lang.String)
223		*/
224		@SuppressWarnings("unchecked")
225		public List<String> getPicNamesByDateFunc(String operator, String operand) {
226	0	PicNameQuery pnq = new PicNameQuery(ds, "DATE(`timestamp`)", operator, operand);
227	0	return(pnq.execute());
228		}
229
230
231		/* (non-Javadoc)
232		* @see com.buckosoft.PicMan.db.PicsDao#getPicNamesByRootFunc(java.lang.String, int)
233		*/
234		@SuppressWarnings("unchecked")
235		public List<String> getPicNamesByRootFunc(String operator, int rid) {
236	0	PicNameQuery pnq = new PicNameQuery(ds, "rid", operator, rid);
237	0	return(pnq.execute());
238		}
239
240		/* @SuppressWarnings("unchecked")
241		public List<String> getPicNamesInSet(Set set) {
242		if (DEBUG)
243		logger.info("Getting pics from set \"" + set.getName() + "\"!");
244		PicsQuery pq = new PicsQuery(ds, set);
245		return(pq.execute());
246		}
247		*/
248
249		/* (non-Javadoc)
250		* @see com.buckosoft.PicMan.db.PicsDao#addPic(com.buckosoft.PicMan.domain.Pic)
251		*/
252		public void addPic(Pic pic) {
253		if (DEBUG)
254		logger.info("addPic: " + pic.getName());
255	0	PicInsert pi = new PicInsert(ds);
256	0	pi.insert(pic);
257	0	Pic pic1 = getPic(pic.getName());
258	0	pic.setPid(pic1.getPid());
259	0	PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
260	0	psu.update(pic);
261	0	}
262
263
264		/** Update this <code>Pic</code> in the database
265		* @param pic The Pic to store
266		*/
267		public void updatePic(Pic pic) throws DataAccessException {
268	0	PicUpdate pu = new PicUpdate(ds);
269	0	pu.update(pic);
270	0	PicMD5SumUpdate psu = new PicMD5SumUpdate(ds);
271	0	psu.update(pic);
272	0	}
273
274		/* (non-Javadoc)
275		* @see com.buckosoft.PicMan.db.PicsDao#getMaxThumbCacheDirUsed()
276		*/
277		@SuppressWarnings("unchecked")
278		public int getMaxThumbCacheDirUsed() {
279	0	PicsQuery pq = new PicsQuery(ds, false);
280	0	List<Pic> list = pq.execute();
281	0	if (list.size() == 0)
282	0	return(0);
283	0	Pic pic = list.get(0);
284	0	if (pic == null)
285	0	return(0);
286	0	return(pic.getCacheDir());
287		}
288
289		public int getPicCount() {
290	0	PicCount pc = new PicCount(ds);
291	0	return(pc.run());
292
293		}
294		/* (non-Javadoc)
295		* @see com.buckosoft.PicMan.db.PicsDao#getPicCount(int)
296		*/
297		public int getPicCount(int rid) {
298	0	PicCount pc = new PicCount(ds, rid, false);
299	0	return(pc.run(rid));
300		}
301
302
303		/* (non-Javadoc)
304		* @see com.buckosoft.PicMan.db.PicsDao#getThumbCacheFillCount(int)
305		*/
306		public int getThumbCacheFillCount(int cacheDir) {
307	0	PicCount pc = new PicCount(ds, cacheDir);
308	0	return(pc.run(cacheDir));
309		}
310
311		///////////////////////////////////////////////////////////////////////////
312		/**
313		* Object to Query for Pics
314		*/
315		private class PicsQuery extends MappingSqlQuery {
316
317	0	PicsQuery(DataSource ds) {
318	0	super(ds, "SELECT * from pics");
319	0	compile();
320	0	}
321
322	0	PicsQuery(DataSource ds, String name) {
323	0	super(ds, "SELECT * from pics where name = ?");
324	0	declareParameter(new SqlParameter(Types.VARCHAR));
325	0	compile();
326	0	}
327	0	PicsQuery(DataSource ds, int pid) {
328	0	super(ds, "SELECT * from pics where pid = ?");
329	0	declareParameter(new SqlParameter(Types.INTEGER));
330	0	compile();
331	0	}
332	0	PicsQuery(DataSource ds, String name, boolean unused) {
333	0	super(ds, "SELECT * from pics where name like ?");
334	0	declareParameter(new SqlParameter(Types.VARCHAR));
335	0	compile();
336	0	}
337		/* PicsQuery(DataSource ds, Set set) {
338		super(ds, "SELECT * from pics where " + set.getName() + " != 0");
339		compile();
340		}
341		*/
342	0	PicsQuery(DataSource ds, Calendar calendar) {
343	0	super(ds, "SELECT * from pics where timestamp > ?");
344	0	declareParameter(new SqlParameter(Types.TIMESTAMP));
345	0	compile();
346	0	}
347	0	PicsQuery(DataSource ds, String dirName, int rid) {
348	0	super(ds, "SELECT * from pics where location = ? AND rid = ? ORDER BY name");
349	0	declareParameter(new SqlParameter(Types.VARCHAR));
350	0	declareParameter(new SqlParameter(Types.INTEGER));
351	0	compile();
352	0	}
353	0	PicsQuery(DataSource ds, DataSource unused) {
354	0	super(ds, "SELECT * from pics ORDER BY timestamp DESC LIMIT 1");
355	0	compile();
356	0	}
357
358		/* PicsQuery(DataSource ds, DataSource unused, boolean unused1) {
359		super(ds, "SELECT * from pics ORDER BY RAND() LIMIT 1");
360		compile();
361		}
362		*/
363	0	PicsQuery(DataSource ds, int rid, long r) {
364	0	super(ds, "SELECT * from pics where rid = ? LIMIT ?, 1");
365	0	declareParameter(new SqlParameter(Types.INTEGER));
366	0	declareParameter(new SqlParameter(Types.INTEGER));
367	0	compile();
368	0	}
369
370	0	PicsQuery(DataSource ds, boolean unused) {
371	0	super(ds, "SELECT * from pics ORDER BY cacheDir DESC LIMIT 1");
372	0	compile();
373	0	}
374		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
375	0	Pic pic = new Pic();
376	0	pic.setName(rs.getString("name"));
377		if (false)
378		logger.info("picName=" + pic.getName());
379		try {
380	0	pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
381	0	} catch (Exception e) {}
382	0	pic.setPid(rs.getInt("pid"));
383	0	pic.setWidth(rs.getInt("width"));
384	0	pic.setHeight(rs.getInt("height"));
385	0	pic.setRid(rs.getInt("rid"));
386	0	pic.setCacheDir(rs.getInt("cacheDir"));
387	0	pic.setLocation(rs.getString("location"));
388	0	return pic;
389		}
390		}
391
392		private class PicMD5SumQuery extends MappingSqlQuery {
393	0	PicMD5SumQuery(DataSource ds, long sum) {
394	0	super(ds, "SELECT * FROM pics p left join picmd5sums s on p.pid = s.pid WHERE s.md5sum = ?");
395	0	declareParameter(new SqlParameter(Types.BIGINT));
396	0	compile();
397	0	}
398		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
399	0	Pic pic = new Pic();
400	0	pic.setName(rs.getString("name"));
401		if (false)
402		logger.info("picName=" + pic.getName());
403		try {
404	0	pic.getDate().setTime(rs.getTimestamp("timestamp").getTime());
405	0	} catch (Exception e) {}
406	0	pic.setPid(rs.getInt("pid"));
407	0	pic.setWidth(rs.getInt("width"));
408	0	pic.setHeight(rs.getInt("height"));
409	0	pic.setRid(rs.getInt("rid"));
410	0	pic.setCacheDir(rs.getInt("cacheDir"));
411	0	pic.setLocation(rs.getString("location"));
412	0	pic.setMd5(rs.getLong("md5sum"));
413	0	return pic;
414		}
415
416		}
417
418		/**
419		* <code>Pic</code> Query Object for just the name.
420		* @author dick
421		*
422		*/
423		private class PicNameQuery extends MappingSqlQuery {
424
425	0	PicNameQuery(DataSource ds) {
426	0	super(ds, "SELECT name FROM pics");
427	0	compile();
428
429	0	}
430	0	PicNameQuery(DataSource ds, String column, String operator, String operand) {
431	0	super(ds, "SELECT name FROM pics WHERE " + column + " " + operator + " \"" + operand + "\"");
432	0	compile();
433		if (DEBUG)
434		logger.info("PicNameQuery() WHERE " + column + " " + operator + " \"" + operand + "\"");
435
436	0	}
437	0	PicNameQuery(DataSource ds, String column, String operator, int operand) {
438	0	super(ds, "SELECT name FROM pics WHERE " + column + " " + operator + " \"" + operand + "\"");
439	0	compile();
440		if (DEBUG)
441		logger.info("PicNameQuery() WHERE " + column + " " + operator + " \"" + operand + "\"");
442
443	0	}
444
445		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
446		// if (DEBUG)
447		// logger.info("row=" + rowNum);
448	0	return(rs.getString("name"));
449		}
450		}
451
452		/**
453		* <code>Pic</code> Count Object.
454		*/
455		private class PicCount extends SqlFunction {
456
457	0	PicCount(DataSource ds) {
458	0	super(ds, "SELECT COUNT(*) from pics");
459	0	compile();
460	0	}
461		/** Count the entries in a cacheDir
462		* @param ds
463		* @param cacheDir
464		*/
465	0	PicCount(DataSource ds, int cacheDir) {
466	0	super(ds, "SELECT COUNT(*) from pics WHERE cacheDir = ?");
467	0	declareParameter(new SqlParameter(Types.INTEGER));
468	0	compile();
469	0	}
470		/** Count the entries in a root
471		* @param ds
472		* @param rid The Root id to query
473		* @param unused a boolean
474		*/
475	0	PicCount(DataSource ds, int rid, boolean unused) {
476	0	super(ds, "SELECT COUNT(*) from pics WHERE rid = ?");
477	0	declareParameter(new SqlParameter(Types.INTEGER));
478	0	compile();
479	0	}
480		}
481
482		/**
483		* <code>Pic</code> Insert Object.
484		*/
485		private class PicInsert extends SqlUpdate {
486
487		/**
488		* Create a new instance of PicInsert.
489		* @param ds the DataSource to use for the insert
490		*/
491	0	protected PicInsert(DataSource ds) {
492	0	super(ds, "INSERT INTO pics VALUES(?,?,?,?,?,?,?,?)");
493	0	declareParameter(new SqlParameter(Types.INTEGER));
494	0	declareParameter(new SqlParameter(Types.VARCHAR));
495	0	declareParameter(new SqlParameter(Types.INTEGER));
496	0	declareParameter(new SqlParameter(Types.INTEGER));
497	0	declareParameter(new SqlParameter(Types.INTEGER));
498	0	declareParameter(new SqlParameter(Types.VARCHAR));
499	0	declareParameter(new SqlParameter(Types.INTEGER));
500	0	declareParameter(new SqlParameter(Types.TIMESTAMP));
501	0	compile();
502	0	}
503
504		protected void insert(Pic p) {
505	0	Object[] objs = new Object[] {
506		new Integer(0),
507	0	p.getName(), new Integer(p.getWidth()), new Integer(p.getHeight()),
508	0	new Integer(p.getRid()), p.getLocation(),
509	0	new Integer(p.getCacheDir()),
510	0	p.getDate()
511		};
512	0	super.update(objs);
513	0	}
514		}
515		///////////////////////////////////////////////////////////////////////////////////////////////
516		/**
517		* <code>Pic</code> Update Object.
518		*/
519		private class PicUpdate extends SqlUpdate {
520		//private DataSource ds;
521
522		/**
523		* Create a new instance of PicUpdate.
524		* @param ds the DataSource to use for the update
525		*/
526	0	protected PicUpdate(DataSource ds) {
527	0	super(ds, "UPDATE pics SET width=?, height=?, rid=?, location=?, cacheDir=?, timestamp=? WHERE name = ? LIMIT 1");
528	0	declareParameter(new SqlParameter(Types.INTEGER));
529	0	declareParameter(new SqlParameter(Types.INTEGER));
530	0	declareParameter(new SqlParameter(Types.INTEGER));
531	0	declareParameter(new SqlParameter(Types.VARCHAR));
532	0	declareParameter(new SqlParameter(Types.INTEGER));
533	0	declareParameter(new SqlParameter(Types.TIMESTAMP));
534	0	declareParameter(new SqlParameter(Types.VARCHAR));
535	0	compile();
536	0	}
537		/**
538		* Method to update the <code>Set</code>'s data.
539		* @param List sets The sets that should be stored
540		* @return 0
541		*/
542
543		protected int update(Pic pic) {
544	0	return this.update(new Object[] {
545	0	new Integer(pic.getWidth()),
546	0	new Integer(pic.getHeight()),
547	0	new Integer(pic.getRid()),
548	0	pic.getLocation(),
549	0	new Integer(pic.getCacheDir()),
550	0	pic.getDate(),
551	0	pic.getName(),
552		});
553		}
554		}
555
556		private class PicMD5SumUpdate extends SqlUpdate {
557	0	protected PicMD5SumUpdate(DataSource ds) {
558	0	super(ds, "REPLACE INTO picmd5sums VALUES(?,?) ");
559	0	declareParameter(new SqlParameter(Types.INTEGER));
560	0	declareParameter(new SqlParameter(Types.BIGINT));
561	0	}
562		protected void update(Pic p) {
563	0	Object[] objs = new Object[] {
564	0	new Integer(p.getPid()),
565	0	new Long(p.getMd5())
566		};
567	0	this.update(objs);
568	0	}
569		}
570
571		}