/com/antlersoft/android/contentxml/SqliteElement.java

  1/**
  2 * Copyright (C) 2010 Michael A. MacDonald
  3 */
  4package com.antlersoft.android.contentxml;
  5
  6import java.io.IOException;
  7import java.io.Reader;
  8import java.io.Writer;
  9import java.util.ArrayList;
 10import java.util.Stack;
 11
 12import android.content.ContentValues;
 13import android.database.Cursor;
 14import android.database.DatabaseUtils;
 15import android.database.SQLException;
 16import android.database.sqlite.SQLiteDatabase;
 17import android.util.Xml;
 18
 19import org.xml.sax.Attributes;
 20import org.xml.sax.ContentHandler;
 21import org.xml.sax.Locator;
 22import org.xml.sax.SAXException;
 23import org.xml.sax.helpers.DefaultHandler;
 24import org.xmlpull.v1.XmlSerializer;
 25
 26import com.antlersoft.util.xml.HandlerStack;
 27import com.antlersoft.util.xml.IElement;
 28import com.antlersoft.util.xml.IHandlerStack;
 29import com.antlersoft.util.xml.SimpleAttributes;
 30
 31/**
 32 * Save or restore the contents of an entire SQLiteDatabase (or entire tables in a SQLite database)
 33 * as an XML element.
 34 * <p>
 35 * This assumes that SQLite table names and
 36 * SQLite column contents can be stored correctly in an XML attribute.
 37 * @author Michael A. MacDonald
 38 *
 39 */
 40public class SqliteElement implements IElement {
 41	
 42	/**
 43	 * android xml parsing does not use an XmlReader that
 44	 * HandlerStack was designed around; instead we'll interpose
 45	 * this content handler layer that manages a stack of
 46	 * ContentHandlers.
 47	 * @author Michael A. MacDonald
 48	 *
 49	 */
 50	public static class StackContentHandler implements ContentHandler, IHandlerStack {
 51
 52		private Stack<ContentHandler> _stack = new Stack<ContentHandler>();
 53		/* (non-Javadoc)
 54		 * @see com.antlersoft.util.xml.IHandlerStack#popHandlerStack()
 55		 */
 56		@Override
 57		public void popHandlerStack() {
 58			_stack.pop();
 59		}
 60
 61		/* (non-Javadoc)
 62		 * @see com.antlersoft.util.xml.IHandlerStack#pushHandlerStack(org.xml.sax.helpers.DefaultHandler)
 63		 */
 64		@Override
 65		public void pushHandlerStack(DefaultHandler handler) {
 66			_stack.push(handler);
 67		}
 68
 69		/* (non-Javadoc)
 70		 * @see com.antlersoft.util.xml.IHandlerStack#startWithHandler(org.xml.sax.helpers.DefaultHandler, java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
 71		 */
 72		@Override
 73		public void startWithHandler(DefaultHandler handler, String uri,
 74				String localName, String qName, Attributes attributes)
 75				throws SAXException {
 76			_stack.push(handler);
 77			handler.startElement(uri, localName, qName, attributes);
 78		}
 79
 80	
 81		/* (non-Javadoc)
 82		 * @see org.xml.sax.ContentHandler#characters(char[], int, int)
 83		 */
 84		@Override
 85		public void characters(char[] arg0, int arg1, int arg2)
 86				throws SAXException {
 87			_stack.peek().characters(arg0, arg1, arg2);
 88		}
 89
 90		/* (non-Javadoc)
 91		 * @see org.xml.sax.ContentHandler#endDocument()
 92		 */
 93		@Override
 94		public void endDocument() throws SAXException {
 95			_stack.peek().endDocument();
 96		}
 97
 98		/* (non-Javadoc)
 99		 * @see org.xml.sax.ContentHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
100		 */
101		@Override
102		public void endElement(String arg0, String arg1, String arg2)
103				throws SAXException {
104			_stack.peek().endElement(arg0, arg1, arg2);
105		}
106
107		/* (non-Javadoc)
108		 * @see org.xml.sax.ContentHandler#endPrefixMapping(java.lang.String)
109		 */
110		@Override
111		public void endPrefixMapping(String arg0) throws SAXException {
112			_stack.peek().endPrefixMapping(arg0);
113		}
114
115		/* (non-Javadoc)
116		 * @see org.xml.sax.ContentHandler#ignorableWhitespace(char[], int, int)
117		 */
118		@Override
119		public void ignorableWhitespace(char[] arg0, int arg1, int arg2)
120				throws SAXException {
121			_stack.peek().ignorableWhitespace(arg0, arg1, arg2);
122		}
123
124		/* (non-Javadoc)
125		 * @see org.xml.sax.ContentHandler#processingInstruction(java.lang.String, java.lang.String)
126		 */
127		@Override
128		public void processingInstruction(String arg0, String arg1)
129				throws SAXException {
130			_stack.peek().processingInstruction(arg0, arg1);
131		}
132
133		/* (non-Javadoc)
134		 * @see org.xml.sax.ContentHandler#setDocumentLocator(org.xml.sax.Locator)
135		 */
136		@Override
137		public void setDocumentLocator(Locator arg0) {
138			_stack.peek().setDocumentLocator(arg0);
139		}
140
141		/* (non-Javadoc)
142		 * @see org.xml.sax.ContentHandler#skippedEntity(java.lang.String)
143		 */
144		@Override
145		public void skippedEntity(String arg0) throws SAXException {
146			_stack.peek().skippedEntity(arg0);
147		}
148
149		/* (non-Javadoc)
150		 * @see org.xml.sax.ContentHandler#startDocument()
151		 */
152		@Override
153		public void startDocument() throws SAXException {
154			_stack.peek().startDocument();
155		}
156
157		/* (non-Javadoc)
158		 * @see org.xml.sax.ContentHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
159		 */
160		@Override
161		public void startElement(String arg0, String arg1, String arg2,
162				Attributes arg3) throws SAXException {
163			_stack.peek().startElement(arg0, arg1, arg2, arg3);
164		}
165
166		/* (non-Javadoc)
167		 * @see org.xml.sax.ContentHandler#startPrefixMapping(java.lang.String, java.lang.String)
168		 */
169		@Override
170		public void startPrefixMapping(String arg0, String arg1)
171				throws SAXException {
172			_stack.peek().startPrefixMapping(arg0, arg1);
173		}
174
175	}
176	private ArrayList<String> _tableNames;
177	SQLiteDatabase _db;
178	private ReplaceStrategy _replaceStrategy;
179	private String _databaseTag;
180	
181	static final String[] TABLE_ARRAY = new String[] { "name" };
182	static final String TABLE_ELEMENT = "table";
183	static final String TABLE_NAME_ATTRIBUTE = "table_name";
184	static final String ROW_ELEMENT = "row";
185	
186	/**
187	 * Determines how existing rows in the table are handled when reading a row from XML causes
188	 * constraint violation.
189	 * @author Michael A. MacDonald
190	 *
191	 */
192	public static enum ReplaceStrategy
193	{
194		/**
195		 * All existing rows are dropped from the table before any rows are added
196		 */
197		REPLACE_ALL,
198		/**
199		 * If an added row causes a constraint violation, drop the existing row.  This is the default
200		 * behavior.  Depends on the existence of the _id column to work correctly.
201		 */
202		REPLACE_EXISTING,
203		/**
204		 * If an added row causes a constraint violation, it is ignored and the existing row is
205		 * preserved.
206		 */
207		REPLACE_NONE
208	}
209	
210	/**
211	 * Convenience function to write a complete database as Xml to a Writer
212	 * @param db
213	 * @param output
214	 * @throws SAXException Problem with Xml serialization
215	 * @throws IOException Problem interacting with output
216	 */
217	public static void exportDbAsXmlToStream(SQLiteDatabase db, Writer output)
218	throws SAXException, IOException
219	{
220		XmlSerializer serializer = Xml.newSerializer();
221		serializer.setOutput(output);
222		new SqliteElement(db, "database").writeToXML(new XmlSerializerHandler(serializer));
223		serializer.flush();
224	}
225	
226	/**
227	 * Convenience function to read a complete database from an XML stream
228	 * @param db Writable database
229	 * @param input Character stream for XML representation of database as written by SqliteElement
230	 * @param replace How to handle existing rows with same id's
231	 * @throws SAXException Problem interpreting XML stream
232	 * @throws IOException Problem interacting with reader
233	 */
234	public static void importXmlStreamToDb(SQLiteDatabase db, Reader input, ReplaceStrategy replace)
235	throws SAXException, IOException
236	{
237		SqliteElement element = new SqliteElement(db, "database");
238		element.setReplaceStrategy(replace);
239		StackContentHandler handler = new StackContentHandler();
240		handler.pushHandlerStack(element.readFromXML(handler));
241		Xml.parse(input, handler);
242	}
243	
244	/**
245	 * 
246	 * @param db Database to save/load
247	 * @param dbName Name of XML element containing entire database
248	 */
249	public SqliteElement(SQLiteDatabase db, String dbName)
250	{
251		_db = db;
252		_tableNames = new ArrayList<String>();
253		_databaseTag = dbName;
254		_replaceStrategy = ReplaceStrategy.REPLACE_EXISTING;
255	}
256	
257	public ReplaceStrategy getReplaceStrategy()
258	{
259		return _replaceStrategy;
260	}
261	
262	public void setReplaceStrategy(ReplaceStrategy newStrategy)
263	{
264		_replaceStrategy = newStrategy;
265	}
266	
267	public void addTable(String tableName)
268	{
269		if (! _tableNames.contains(tableName))
270		{
271			_tableNames.add(tableName);
272		}
273	}
274	
275	public void removeTable(String tableName)
276	{
277		getTableNames().remove(tableName);
278	}
279	
280	ArrayList<String> getTableNames()
281	{
282		if (_tableNames.size() == 0)
283		{
284			Cursor c = _db.query("sqlite_master", TABLE_ARRAY, "type = 'table'", null, null, null, null);
285			try
286			{
287				while (c.moveToNext())
288				{
289					String t = c.getString(0);
290					String test = t.toLowerCase();
291					if (! test.equals("android_metadata") && ! test.equals("sqlite_sequence"))
292					{
293						_tableNames.add(t);
294					}
295				}
296			}
297			finally
298			{
299				c.close();
300			}
301		}
302		return _tableNames;
303	}
304
305	/* (non-Javadoc)
306	 * @see com.antlersoft.util.xml.IElement#getElementTag()
307	 */
308	@Override
309	public String getElementTag() {
310		return _databaseTag;
311	}
312
313	/* (non-Javadoc)
314	 * @see com.antlersoft.util.xml.IElement#readFromXML(com.antlersoft.util.xml.IHandlerStack)
315	 */
316	@Override
317	public DefaultHandler readFromXML(IHandlerStack handlerStack) {
318		return new SqliteElementHandler(handlerStack);
319	}
320
321	/* (non-Javadoc)
322	 * @see com.antlersoft.util.xml.IElement#writeToXML(org.xml.sax.ContentHandler)
323	 */
324	@Override
325	public void writeToXML(ContentHandler xmlWriter) throws SAXException {
326		xmlWriter.startElement("", "", getElementTag(), null);
327		for (String s : getTableNames())
328		{
329			SimpleAttributes attributes = new SimpleAttributes();
330			attributes.addValue(TABLE_NAME_ATTRIBUTE, s);
331			xmlWriter.startElement("", "", TABLE_ELEMENT, attributes.getAttributes());
332			Cursor c = _db.query(s, null, null, null, null, null, null);
333			try
334			{
335				if (c.moveToFirst())
336				{
337					ContentValues cv = new ContentValues();
338					do
339					{
340						DatabaseUtils.cursorRowToContentValues(c, cv);
341						new ContentValuesElement(cv, ROW_ELEMENT).writeToXML(xmlWriter);
342					}
343					while (c.moveToNext());
344				}
345			}
346			finally
347			{
348				c.close();
349			}
350			xmlWriter.endElement("", "", TABLE_ELEMENT);
351		}
352		xmlWriter.endElement("", "", getElementTag());
353	}
354
355	/**
356	 * DefaultHandler implementation that puts recognized XML into a database
357	 * 
358	 * @author Michael A. MacDonald
359	 *
360	 */
361	class SqliteElementHandler extends DefaultHandler {
362		
363		private IHandlerStack _stack;
364		private String _currentTable;
365		private ContentValues _lastRow;
366		private static final long INSERT_FAILED = -1L;
367		
368		public SqliteElementHandler(IHandlerStack handlerStack) {
369			_stack = handlerStack;
370		}
371
372		/* (non-Javadoc)
373		 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
374		 */
375		@Override
376		public void endElement(String uri, String localName, String qName)
377				throws SAXException {
378			saveLastRow();
379		}
380
381		/* (non-Javadoc)
382		 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
383		 */
384		@Override
385		public void startElement(String uri, String localName, String qName,
386				Attributes attributes) throws SAXException {
387			saveLastRow();
388			if (localName.equals(TABLE_ELEMENT))
389			{
390				_currentTable = attributes.getValue(TABLE_NAME_ATTRIBUTE);
391				if (_currentTable == null)
392				{
393					throw new SAXException(TABLE_NAME_ATTRIBUTE + " not found in " + TABLE_ELEMENT + " element.");
394				}
395				if (getTableNames().contains(_currentTable))
396				{
397					if (getReplaceStrategy() == ReplaceStrategy.REPLACE_ALL)
398					{
399						_db.delete(_currentTable, null, null);
400					}
401				}
402				else
403					_currentTable = null;
404			}
405			else if (localName.equals(ROW_ELEMENT))
406			{
407				_lastRow = new ContentValues();
408				ContentValuesElement rowElement = new ContentValuesElement(_lastRow, ROW_ELEMENT);
409				_stack.startWithHandler(rowElement.readFromXML(_stack), uri, localName, qName, attributes);
410			}
411		}
412
413		private void saveLastRow()
414		{
415			if (_lastRow != null)
416			{
417				try
418				{
419					// See if it is a table we care about
420					if (_currentTable != null)
421					{
422						long id = _db.insert(_currentTable, null, _lastRow);
423						if (id == INSERT_FAILED)
424						{
425							switch (getReplaceStrategy())
426							{
427							case REPLACE_ALL :
428								throw new SQLException("Failed to insert row in "+_currentTable+" after emptying");
429							case REPLACE_EXISTING :
430								_db.delete(_currentTable, "_id = ?", new String[] { _lastRow.getAsString("_id") });
431								_db.insertOrThrow(_currentTable, null, _lastRow);
432								break;
433							}
434						}
435					}
436				}
437				finally
438				{
439					_lastRow = null;
440				}
441			}
442		}
443	}
444	/**
445	 * Copies data to an XmlSerializer; not complete; only implemented enough to work with
446	 * SqliteElement
447	 * @author Michael A. MacDonald
448	 *
449	 */
450	static class XmlSerializerHandler extends DefaultHandler {
451		
452		XmlSerializer _serializer;
453		boolean _first;
454		
455		XmlSerializerHandler(XmlSerializer serializer)
456		{
457			_serializer = serializer;
458			_first = true;
459		}
460
461		/* (non-Javadoc)
462		 * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
463		 */
464		@Override
465		public void characters(char[] ch, int start, int length)
466				throws SAXException {
467			try
468			{
469				_serializer.text(ch, start, length);
470			}
471			catch (IOException ioe)
472			{
473				throw new SAXException(ioe.getMessage(), ioe);
474			}
475		}
476
477		/* (non-Javadoc)
478		 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
479		 */
480		@Override
481		public void endElement(String uri, String localName, String qName)
482				throws SAXException {
483			try
484			{
485				_serializer.endTag(uri, qName);
486			}
487			catch (IOException ioe)
488			{
489				throw new SAXException(ioe.getMessage(), ioe);
490			}
491		}
492
493		/* (non-Javadoc)
494		 * @see org.xml.sax.helpers.DefaultHandler#ignorableWhitespace(char[], int, int)
495		 */
496		@Override
497		public void ignorableWhitespace(char[] ch, int start, int length)
498				throws SAXException {
499			try
500			{
501				_serializer.ignorableWhitespace(new String(ch, start, length));
502			}
503			catch (IOException ioe)
504			{
505				throw new SAXException(ioe.getMessage(), ioe);
506			}
507		}
508
509		/* (non-Javadoc)
510		 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
511		 */
512		@Override
513		public void startElement(String uri, String localName, String qName,
514				Attributes attributes) throws SAXException {
515			try
516			{
517				if (_first)
518					_first = false;
519				else
520					_serializer.ignorableWhitespace("\r\n");
521				_serializer.startTag(uri, qName);
522				if (attributes != null)
523				{
524					int l = attributes.getLength();
525					for (int i = 0; i < l; i++)
526					{
527						_serializer.attribute(attributes.getURI(i), attributes.getQName(i), attributes.getValue(i));
528					}
529				}
530			}
531			catch (IOException ioe)
532			{
533				throw new SAXException(ioe.getMessage(), ioe);
534			}
535		}
536
537	}
538
539}