/edu/uncc/parsets/data/LocalDBDataSet.java

  1package edu.uncc.parsets.data;
  2
  3import edu.uncc.parsets.parsets.SelectionChangeEvent;
  4import java.sql.ResultSet;
  5import java.sql.SQLException;
  6import java.sql.Statement;
  7import java.util.ArrayList;
  8import java.util.Iterator;
  9import java.util.List;
 10
 11import edu.uncc.parsets.data.LocalDB.DBAccess;
 12import edu.uncc.parsets.util.PSLogging;
 13
 14/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
 15 * Copyright (c) 2009, Robert Kosara, Caroline Ziemkiewicz,
 16 *                     and others (see Authors.txt for full list)
 17 * All rights reserved.
 18 * 
 19 * Redistribution and use in source and binary forms, with or without
 20 * modification, are permitted provided that the following conditions are met:
 21 * 
 22 *    * Redistributions of source code must retain the above copyright
 23 *      notice, this list of conditions and the following disclaimer.
 24 *    * Redistributions in binary form must reproduce the above copyright
 25 *      notice, this list of conditions and the following disclaimer in the
 26 *      documentation and/or other materials provided with the distribution.
 27 *    * Neither the name of UNC Charlotte nor the names of its contributors
 28 *      may be used to endorse or promote products derived from this software
 29 *      without specific prior written permission.
 30 *      
 31 * THIS SOFTWARE IS PROVIDED BY ITS AUTHORS ''AS IS'' AND ANY
 32 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 33 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 34 * DISCLAIMED. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY
 35 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 36 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 37 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 38 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 39 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
 40 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 41\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
 42
 43/**
 44 * A dataset that is backed by a SQLite database.
 45 */
 46public class LocalDBDataSet extends DataSet {
 47
 48	/**
 49	 * Whether to write a timestamp to the DB whenever a dataset is opened.
 50	 * This is not very useful for the Java program, but will be important
 51	 * on the iPhone.
 52	 */
 53	private static final boolean RECORDOPENTIMES = false;
 54	
 55	private LocalDB db;
 56	
 57	private String dbHandle;
 58	
 59	private ArrayList<DimensionHandle> dimHandles;
 60	
 61	private int numRecords;
 62
 63	private String url = "";
 64
 65	private String section = "Misc";
 66
 67	private String source = "";
 68	
 69	private String srcURL = "";
 70	
 71	protected LocalDBDataSet(String dsName, String handle, int count, String remoteURL, String listSection, String dbSource, String dbSrcURL, LocalDB dbHandler) {
 72		name = dsName;
 73		dbHandle = handle;
 74		db = dbHandler;
 75		numRecords = count;
 76		if (remoteURL != null)
 77			url = remoteURL;
 78		if (listSection != null)
 79			section = listSection;
 80		if (dbSource != null)
 81			source = dbSource;
 82		if (dbSrcURL != null)
 83			srcURL = dbSrcURL;
 84	}
 85	
 86	public LocalDB getDB() {
 87		return db;
 88	}
 89	
 90	@Override
 91	public int getNumRecords() {
 92		return numRecords;
 93	}
 94	
 95	@Override
 96	public String toString() {
 97		return name;
 98	}
 99	
100	@Override
101	public String getURL() {
102		return url;
103	}
104	
105	public String getSection() {
106		return section;
107	}
108		
109	public String getSource() {
110		return source;
111	}
112	
113	public String getSrcURL() {
114		return srcURL;
115	}
116	
117	@Override
118	public CategoryTree getTree(List<DimensionHandle> dimensions) {
119		CategoryTree tree = new CategoryTree(dimensions.size()+1);
120		String dimList = dims2String(dimensions);
121		StringBuffer query = new StringBuffer("select ");
122		query.append(dimList);
123		query.append(", sum(count) from "+dbHandle+"_dims group by ");
124		query.append(dimList);
125		query.append(";");
126
127		try {
128			Statement stmt = db.createStatement(DBAccess.FORREADING);
129			ResultSet rs = stmt.executeQuery(query.toString());
130			CategoryNode thisLine[] = new CategoryNode[dimensions.size()+1];
131			CategoryNode previousLine[] = new CategoryNode[dimensions.size()+1];
132			CategoryNode root = new CategoryNode(null, null, 0);
133			tree.addtoLevel(0, root);
134			// this would be much prettier as a recursive function, but the
135			// semantics and side effects of the ResultSet make that very
136			// annoying
137			while (rs.next()) {
138				int column = 1;
139				CategoryNode previousNode = root;
140				for (DimensionHandle dim : dimensions) {
141					CategoryHandle cat = dim.num2Handle(rs.getInt(column));
142					CategoryNode node = null;
143					if (previousLine[column] != null && cat == previousLine[column].getToCategory()) {
144						node = previousLine[column];
145					} else {
146						if (column == dimensions.size())
147							node = new CategoryNode(previousNode, cat, rs.getInt(column+1));
148						else {
149							node = new CategoryNode(previousNode, cat, 0);
150							for (int i = column+1; i <= dimensions.size(); i++)
151								previousLine[i] = null;
152						}
153						tree.addtoLevel(column, node);
154					}
155					previousNode = node;
156					thisLine[column] = node;
157					
158					column++;
159				}
160				CategoryNode temp[] = thisLine;
161				thisLine = previousLine;
162				previousLine = temp;
163			}
164			rs.close();			
165		} catch (SQLException e) {
166			PSLogging.logger.error("SQL error while creating tree.", e);
167		} finally {
168			db.releaseReadLock();
169		}
170		tree.getRootNode().updateValues();
171		return tree;
172	}
173
174	/**
175	 * Makes a string from a list of dimensions, with commas in between. Returns
176	 * an empty string for an empty list.
177	 * 
178	 * @param dimensions List of dimension handles to put in the string
179	 * @return SQL-ready list of dimension handles
180	 */
181	public static String dims2String(List<DimensionHandle> dimensions) {
182		StringBuffer dimList = new StringBuffer();
183		boolean first = true;
184		for (DimensionHandle handle : dimensions) {
185			if (first)
186				first = false;
187			else
188				dimList.append(", ");
189			dimList.append(handle.getHandle());
190		}
191		return dimList.toString();
192	}
193
194	@Override
195	public Iterator<DimensionHandle> iterator() {
196		if (dimHandles == null)
197			loadDimensions();
198		return dimHandles.iterator();
199	}
200	
201	private void loadDimensions() {
202		try {
203			Statement stmt = db.createStatement(DBAccess.FORREADING);
204			dimHandles = new ArrayList<DimensionHandle>();
205			ResultSet rs = stmt.executeQuery("select name, handle, type from Admin_Dimensions where dataSet = '"+dbHandle+"';");
206			int dimNum = 0;
207			while (rs.next()) {
208				dimHandles.add(new DimensionHandle(rs.getString(1), rs.getString(2), DataType.typeFromString(rs.getString(3)), dimNum, this));
209				dimNum++;
210			}
211			rs.close();
212			if (RECORDOPENTIMES)
213				stmt.executeUpdate("update Admin_DataSets set lastOpened=datetime('now') where handle='"+dbHandle+"';");
214		} catch (SQLException e) {
215			PSLogging.logger.error("SQL error while loading dimensions.", e);
216		} finally {
217			db.releaseReadLock();
218		}
219	}
220	
221	public void deleteFromDB() {
222		db.deleteFromDB(dbHandle);
223	}
224
225	@Override
226	public String getName() {
227		return name;
228	}
229	
230	// changed protected to public temporary
231	@Override
232	public String getHandle() {
233		return dbHandle;
234	}
235
236	public int getNumDimensions() {
237		if (dimHandles == null)
238			loadDimensions();
239
240		return dimHandles.size();
241	}
242	
243	public int getNumCategoricalDimensions() {
244		if (dimHandles == null)
245			loadDimensions();
246		int num = 0;
247		for (DimensionHandle d : dimHandles)
248			if (d.getDataType() == DataType.categorical) 
249				num++;
250		return num;
251	}
252	
253	public int getNumNumericDimensions() {
254		return getNumDimensions()-getNumCategoricalDimensions();
255	}
256
257	public DimensionHandle[] getNumericDimensions() {
258		DimensionHandle handles[] = new DimensionHandle[getNumNumericDimensions()];
259		int i = 0;
260		for (DimensionHandle d : dimHandles)
261			if (d.getDataType() != DataType.categorical)
262				handles[i++] = d;
263		
264		return handles;
265	}
266
267    public void selectionChanged(SelectionChangeEvent event) {
268        
269    }
270    
271    // new method
272    public ArrayList<DimensionHandle> getDimensions(){
273    	return dimHandles;
274    }
275}