PageRenderTime 31ms CodeModel.GetById 12ms app.highlight 15ms RepoModel.GetById 1ms app.codeStats 0ms

/edu/uncc/parsets/data/JSONExport.java

https://code.google.com/p/parsets/
Java | 255 lines | 191 code | 16 blank | 48 comment | 19 complexity | e463b0d71b1d60ba8ea1bf303b28c15d MD5 | raw file
  1package edu.uncc.parsets.data;
  2
  3import java.io.FileOutputStream;
  4import java.io.OutputStreamWriter;
  5import java.sql.PreparedStatement;
  6import java.sql.ResultSet;
  7import java.sql.SQLException;
  8import java.sql.Types;
  9import java.util.ArrayList;
 10import java.util.LinkedHashMap;
 11import java.util.List;
 12import java.util.Map;
 13import java.util.Vector;
 14import java.util.zip.Deflater;
 15import java.util.zip.GZIPOutputStream;
 16
 17import org.json.simple.JSONValue;
 18
 19import edu.uncc.parsets.data.LocalDB.DBAccess;
 20import edu.uncc.parsets.util.PSLogging;
 21
 22/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *\
 23 * Copyright (c) 2009, Robert Kosara, Caroline Ziemkiewicz,
 24 *                     and others (see Authors.txt for full list)
 25 * All rights reserved.
 26 * 
 27 * Redistribution and use in source and binary forms, with or without
 28 * modification, are permitted provided that the following conditions are met:
 29 * 
 30 *    * Redistributions of source code must retain the above copyright
 31 *      notice, this list of conditions and the following disclaimer.
 32 *    * Redistributions in binary form must reproduce the above copyright
 33 *      notice, this list of conditions and the following disclaimer in the
 34 *      documentation and/or other materials provided with the distribution.
 35 *    * Neither the name of UNC Charlotte nor the names of its contributors
 36 *      may be used to endorse or promote products derived from this software
 37 *      without specific prior written permission.
 38 *      
 39 * THIS SOFTWARE IS PROVIDED BY ITS AUTHORS ''AS IS'' AND ANY
 40 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 41 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 42 * DISCLAIMED. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY
 43 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 44 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 45 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 46 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 47 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
 48 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 49\* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
 50
 51/**
 52 * Export {@link DataSet}s to a streaming-friendly JSON format.
 53 */
 54public class JSONExport {
 55
 56	/**
 57	 * The version of the JSON data produced by this class. The idea is to
 58	 * treat 1000 as 1.000, 1100 as 1.1, etc. Minor revisions that don't break
 59	 * compatibility in the reader increment the last two digits. A revision
 60	 * that requires an update of the reader adds 100. A change in the first
 61	 * digit would mean a complete revamp of the data model.
 62	 */
 63	public static final int JSONVERSION = 2000;
 64
 65	public static final int JSONMAXCOMPATIBLEVERSION = 2099;
 66	
 67	public static final String METAKEY = "%meta";
 68
 69	public static final String DATASETSKEY = "datasets";
 70	
 71	public static String exportDataSet(LocalDBDataSet ds, String fileName) {
 72		Map<String, Object> dataset = new LinkedHashMap<String, Object>();
 73		Map<String, Object> meta = new LinkedHashMap<String, Object>();
 74		meta.put("version", JSONExport.JSONVERSION);
 75		meta.put("type", "cube");
 76		List<Map<String, Object>> tables = new Vector<Map<String, Object>>();
 77		meta.put("tables", tables);
 78		List<Map<String, Object>> indices = new Vector<Map<String, Object>>();
 79		meta.put("indices", indices);
 80		dataset.put(METAKEY, meta);
 81		Map<String, List<List<Object>>> data = new LinkedHashMap<String, List<List<Object>>>();
 82		dataset.put("data", data);
 83		try {
 84			exportTable(data, ds.getDB(), "Admin_Datasets", "handle", ds.getHandle());
 85			exportTable(data, ds.getDB(), "Admin_Dimensions", "dataset", ds.getHandle());
 86			exportTable(data, ds.getDB(), "Admin_Categories", "dataset", ds.getHandle());
 87			PreparedStatement stmt = ds.getDB().prepareStatement("select name, sql, type from sqlite_master where name like \""+ds.getHandle()+"%\";", DBAccess.FORREADING);
 88			ResultSet rs = stmt.executeQuery();
 89			while (rs.next()) {
 90				if (rs.getString("type").equals("table")) {
 91					tables.add(create2JSON(rs.getString("sql")));
 92					String tableName = rs.getString("name");
 93					exportTable(data, ds.getDB(), tableName, null, null);
 94				} else if (rs.getString("sql") != null) {
 95					indices.add(index2JSON(rs.getString("sql")));
 96				}
 97			}
 98			return writeGZIPFile(fileName, dataset);
 99		} catch (Exception e) {
100			PSLogging.logger.error("Could not export dataset as JSON file.", e);
101		} finally {
102			ds.getDB().releaseReadLock();
103		}
104		return null;
105	}
106
107	private static void exportTable(Map<String, List<List<Object>>> container, LocalDB db, String tableName, String key, String value) {
108		List<List<Object>> data = new ArrayList<List<Object>>();
109		try {
110			String sql = "select * from "+tableName;
111			if (key != null)
112				sql += " where "+key+"='"+value+"';";
113			else
114				sql += ";";
115			PreparedStatement stmt = db.prepareStatement(sql, DBAccess.FORREADING);
116			ResultSet rs = stmt.executeQuery();
117			boolean firstRow = true;
118			int numColumns = 0;
119			while (rs.next()) {
120				if (firstRow) {
121					numColumns = rs.getMetaData().getColumnCount();
122					List<Object> row = new ArrayList<Object>(numColumns);
123					for (int column = 1; column <= numColumns; column++) {
124						switch (rs.getMetaData().getColumnType(column)) {
125						case Types.INTEGER:
126							row.add("INTEGER");
127							break;
128						case Types.FLOAT:
129							row.add("REAL");
130							break;
131						case Types.NULL: // null can only be an empty text field
132						case Types.VARCHAR:
133							row.add("TEXT");
134							break;
135						default:
136							PSLogging.logger.error("Encountered unknown column type: "+rs.getMetaData().getColumnType(column)+" in table "+tableName);
137						}
138					}
139					data.add(row);
140					firstRow = false;
141				}
142				List<Object> row = new ArrayList<Object>(numColumns);
143				for (int column = 1; column <= numColumns; column++) {
144					switch (rs.getMetaData().getColumnType(column)) {
145					case Types.INTEGER:
146						row.add(rs.getLong(column));
147						break;
148					case Types.FLOAT:
149						row.add(rs.getFloat(column));
150						break;
151					case Types.VARCHAR:
152						row.add(rs.getString(column));
153						break;
154					case Types.NULL:
155						row.add(null);
156						break;
157					}
158				}
159				data.add(row);
160			}
161			container.put(tableName, data);
162		} catch (SQLException e) {
163			PSLogging.logger.error("Could not query table "+tableName+".", e);
164		} finally {
165			db.releaseReadLock();
166		}
167	}	
168	
169	private static Map<String, Object> create2JSON(String sql) {
170		Map<String, Object> json = new LinkedHashMap<String, Object>();
171		// CREATE TABLE Admin_Dimensions (dataSet TEXT, name TEXT, handle TEXT, type TEXT, leftShift INTEGER, bitMask INTEGER)
172		// CREATE TABLE householdsal_measures (key INTEGER, numpeople REAL, numvehicles REAL, costelectricity REAL, costgas REAL, costwater REAL, costoil REAL, rent REAL, mortgage REAL, mortgage2 REAL, rentaspercentage REAL, employment REAL, experience REAL, totalincome REAL)
173		String firstSplit[] = sql.split("\\(");
174		// extract table name from "CREATE TABLE <tablename>"
175		String create[] = firstSplit[0].split(" ");
176		json.put("tableName", create[2]);
177		// fields are "<name>", "<type>," pairs, last one ends in ")" instead of comma
178		String columnNames[] = firstSplit[1].split(" ");
179		List<List<String>> columns = new ArrayList<List<String>>(columnNames.length/2);
180		for (int i = 0; i < columnNames.length; i += 2) {
181			List<String> pair = new ArrayList<String>(2);
182			pair.add(columnNames[i]);
183			pair.add(columnNames[i+1].substring(0, columnNames[i+1].length()-1));
184			columns.add(pair);
185		}
186		json.put("columns", columns);
187		return json;
188	}
189	
190	private static Map<String, Object> index2JSON(String sql) {
191		Map<String, Object> json = new LinkedHashMap<String, Object>();
192		String tokens[] = sql.split(" ");
193		// CREATE INDEX Admin_Dimensions_Handle on Admin_Dimensions (dataSet)
194		// CREATE INDEX Admin_Categories_DSHandle on Admin_Categories (dataSet, dimension)
195		// skip "CREATE" and "INDEX"
196		json.put("indexName", tokens[2]);
197		// skip "on"
198		json.put("tableName", tokens[4]);
199		// now remove parentheses and commas from rest
200		List<String> columns = new ArrayList<String>(tokens.length-5);
201		for (int i = 5; i < tokens.length; i++) {
202			String column = tokens[i].substring(0, tokens[i].length()-1);
203			if (i == 5)
204				column = column.substring(1);
205			columns.add(column);
206		}
207		json.put("columns", columns);		
208		return json;
209	}
210	
211	public static String exportDBIndex(LocalDB db, String fileName) {
212		Map<String, Object> index = new LinkedHashMap<String, Object>();
213		Map<String, Object> meta = new LinkedHashMap<String, Object>();
214		meta.put("type", "index");
215		meta.put("version", JSONExport.JSONVERSION);
216		index.put(METAKEY, meta);
217		Map<String, Map<String, Object>> dsList = new LinkedHashMap<String, Map<String,Object>>();
218		for (DataSet ds : db.getDataSets()) {
219			Map<String, Object> dataSet = new LinkedHashMap<String, Object>();
220			dataSet.put("name", ds.getName());
221			dataSet.put("section", ds.getSection());
222			dataSet.put("source", ((LocalDBDataSet)ds).getSource());
223			dataSet.put("srcURL", ((LocalDBDataSet)ds).getSrcURL());
224			dataSet.put("items", ds.getNumRecords());
225			dataSet.put("categorical", ds.getNumCategoricalDimensions());
226			dataSet.put("numerical", ds.getNumNumericDimensions());
227			dataSet.put("url", ds.getURL());
228			dsList.put(ds.getHandle(), dataSet);
229		}
230		index.put(DATASETSKEY, dsList);
231		return writeGZIPFile(fileName, index);
232	}
233	
234	private static String writeGZIPFile(String fileName, Map<String, Object> dataset) {
235		if (!fileName.endsWith(".json.gz"))
236			fileName += ".json.gz";
237		try {
238			// found this trick on http://weblogs.java.net/blog/mister__m/archive/2003/12/achieving_bette.html
239			GZIPOutputStream outStream = new GZIPOutputStream(new FileOutputStream(fileName)) {
240				{
241					def.setLevel(Deflater.BEST_COMPRESSION);
242				}
243			};
244			OutputStreamWriter outWriter = new OutputStreamWriter(outStream);
245			JSONValue.writeJSONString(dataset, outWriter);
246			outWriter.flush();
247			outWriter.close();
248			return fileName;
249		} catch (Exception e) {
250			PSLogging.logger.error("Error writing to gzipped file "+fileName+".", e);
251		}
252		return null;
253	}
254	
255}