Differential D17 Diff 56 src/it/reyboz/bustorino/middleware/UserDB.java

Changeset View

Standalone View

src/it/reyboz/bustorino/middleware/UserDB.java

	/*			/*
	BusTO ("backend" components)			BusTO ("backend" components)
	Copyright (C) 2016 Ludovico Pavesi			Copyright (C) 2016 Ludovico Pavesi

	This program is free software: you can redistribute it and/or modify			This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by			it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or			the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.			(at your option) any later version.

	This program is distributed in the hope that it will be useful,			This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of			but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the			MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	GNU General Public License for more details.			GNU General Public License for more details.

	You should have received a copy of the GNU General Public License			You should have received a copy of the GNU General Public License
	along with this program. If not, see <http://www.gnu.org/licenses/>.			along with this program. If not, see <http://www.gnu.org/licenses/>.
	*/			*/

	package it.reyboz.bustorino.middleware;			package it.reyboz.bustorino.middleware;

	import android.content.ContentValues;			import android.content.ContentValues;
	import android.database.Cursor;			import android.database.Cursor;
	import android.database.sqlite.SQLiteDatabase;			import android.database.sqlite.SQLiteDatabase;
	import android.database.sqlite.SQLiteException;			import android.database.sqlite.SQLiteException;
	import android.database.sqlite.SQLiteOpenHelper;			import android.database.sqlite.SQLiteOpenHelper;
	import android.content.Context;			import android.content.Context;
	import android.util.Log;			import android.util.Log;

	import java.util.ArrayList;			import java.util.ArrayList;
	import java.util.Collections;			import java.util.Collections;
	import java.util.List;			import java.util.List;

	import it.reyboz.bustorino.backend.Stop;			import it.reyboz.bustorino.backend.Stop;
	import it.reyboz.bustorino.backend.StopsDBInterface;			import it.reyboz.bustorino.backend.StopsDBInterface;

	public class UserDB extends SQLiteOpenHelper {			public class UserDB extends SQLiteOpenHelper {
	public static final int DATABASE_VERSION = 1;			public static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "user.db";			private static final String DATABASE_NAME = "user.db";
	static final String TABLE_NAME = "favorites";			static final String TABLE_NAME = "favorites";
	private final Context c; // needed during upgrade			private final Context c; // needed during upgrade
	private final static String[] usernameColumnNameAsArray = {"username"};			private final static String[] usernameColumnNameAsArray = {"username"};
	public final static String[] getFavoritesColumnNamesAsArray = {"ID", "username"};			public final static String[] getFavoritesColumnNamesAsArray = {"ID", "username"};

	public UserDB(Context context) {			public UserDB(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);			super(context, DATABASE_NAME, null, DATABASE_VERSION);
	this.c = context;			this.c = context;
	}			}

	@Override			@Override
	public void onCreate(SQLiteDatabase db) {			public void onCreate(SQLiteDatabase db) {
	// exception intentionally left unhandled			// exception intentionally left unhandled
	db.execSQL("CREATE TABLE favorites (ID TEXT PRIMARY KEY NOT NULL, username TEXT)");			db.execSQL("CREATE TABLE favorites (ID TEXT PRIMARY KEY NOT NULL, username TEXT)");

	if(OldDB.doesItExist(this.c)) {			if(OldDB.doesItExist(this.c)) {
	upgradeFromOldDatabase(db);			upgradeFromOldDatabase(db);
	}			}
	}			}

	private void upgradeFromOldDatabase(SQLiteDatabase newdb) {			private void upgradeFromOldDatabase(SQLiteDatabase newdb) {
	OldDB old;			OldDB old;
	try {			try {
	old = new OldDB(this.c);			old = new OldDB(this.c);
	} catch(IllegalStateException e) {			} catch(IllegalStateException e) {
	// can't create database => it doesn't really exist, no matter what doesItExist() says			// can't create database => it doesn't really exist, no matter what doesItExist() says
	return;			return;
	}			}

	int ver = old.getOldVersion();			int ver = old.getOldVersion();

	/* version 8 was the previous version, OldDB "upgrades" itself to 1337 but unless the app			/* version 8 was the previous version, OldDB "upgrades" itself to 1337 but unless the app
	* has crashed midway through the upgrade and the user is retrying, that should never show			* has crashed midway through the upgrade and the user is retrying, that should never show
	* up here. And if it does, try to recover favorites anyway.			* up here. And if it does, try to recover favorites anyway.
	* Versions < 8 already got dropped during the update process, so let's do the same.			* Versions < 8 already got dropped during the update process, so let's do the same.
	*			*
	* Edit: Android runs getOldVersion() then, after a while, onUpgrade(). Just to make it			* Edit: Android runs getOldVersion() then, after a while, onUpgrade(). Just to make it
	* more complicated. Workaround added in OldDB.			* more complicated. Workaround added in OldDB.
	*/			*/
	if(ver >= 8) {			if(ver >= 8) {
	ArrayList<String> ID = new ArrayList<>();			ArrayList<String> ID = new ArrayList<>();
	ArrayList<String> username = new ArrayList<>();			ArrayList<String> username = new ArrayList<>();
	int len;			int len;
	int len2;			int len2;

	try {			try {
	Cursor c = old.getReadableDatabase().rawQuery("SELECT busstop_ID, busstop_username FROM busstop WHERE busstop_isfavorite = 1 ORDER BY busstop_name ASC", new String[] {});			Cursor c = old.getReadableDatabase().rawQuery("SELECT busstop_ID, busstop_username FROM busstop WHERE busstop_isfavorite = 1 ORDER BY busstop_name ASC", new String[] {});

	int zero = c.getColumnIndex("busstop_ID");			int zero = c.getColumnIndex("busstop_ID");
	int one = c.getColumnIndex("busstop_username");			int one = c.getColumnIndex("busstop_username");

	while(c.moveToNext()) {			while(c.moveToNext()) {
	try {			try {
	ID.add(c.getString(zero));			ID.add(c.getString(zero));
	} catch(Exception e) {			} catch(Exception e) {
	// no ID = can't add this			// no ID = can't add this
	continue;			continue;
	}			}

	if(c.getString(one) == null \|\| c.getString(one).length() <= 0) {			if(c.getString(one) == null \|\| c.getString(one).length() <= 0) {
	username.add(null);			username.add(null);
	} else {			} else {
	username.add(c.getString(one));			username.add(c.getString(one));
	}			}
	}			}

	c.close();			c.close();
	old.close();			old.close();
	} catch(Exception ignored) {			} catch(Exception ignored) {
	// there's no hope, go ahead and nuke old database.			// there's no hope, go ahead and nuke old database.
	}			}

	len = ID.size();			len = ID.size();
	len2 = username.size();			len2 = username.size();
	if(len2 < len) {			if(len2 < len) {
	len = len2;			len = len2;
	}			}


	if (len > 0) {			if (len > 0) {

	try {			try {
	Stop stopStopStopStopStop;			Stop stopStopStopStopStop;
	for (int i = 0; i < len; i++) {			for (int i = 0; i < len; i++) {
	stopStopStopStopStop = new Stop(ID.get(i));			stopStopStopStopStop = new Stop(ID.get(i));
	stopStopStopStopStop.setStopUserName(username.get(i));			stopStopStopStopStop.setStopUserName(username.get(i));
	addOrUpdateStop(stopStopStopStopStop, newdb);			addOrUpdateStop(stopStopStopStopStop, newdb);
	}			}
	} catch(Exception ignored) {			} catch(Exception ignored) {
	// partial data is better than no data at all, no transactions here			// partial data is better than no data at all, no transactions here
	}			}
	}			}
	}			}

	if(!OldDB.destroy(this.c)) {			if(!OldDB.destroy(this.c)) {
	// TODO: notify user somehow?			// TODO: notify user somehow?
	Log.e("UserDB", "Failed to delete old database, you should really uninstall and reinstall the app. Unfortunately I have no way to tell the user.");			Log.e("UserDB", "Failed to delete old database, you should really uninstall and reinstall the app. Unfortunately I have no way to tell the user.");
	}			}
	}			}

	@Override			@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {			public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// nothing to do yet			// nothing to do yet
	}			}

	@Override			@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {			public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// nothing to do yet			// nothing to do yet
	}			}

	/**			/**
	* Check if a stop ID is in the favorites			* Check if a stop ID is in the favorites
	*			*
	* @param db readable database			* @param db readable database
	* @param stopId stop ID			* @param stopId stop ID
	* @return boolean			* @return boolean
	*/			*/
	public static boolean isStopInFavorites(SQLiteDatabase db, String stopId) {			public static boolean isStopInFavorites(SQLiteDatabase db, String stopId) {
	boolean found = false;			boolean found = false;

	try {			try {
	Cursor c = db.query(TABLE_NAME, usernameColumnNameAsArray, "ID = ?", new String[] {stopId}, null, null, null);			Cursor c = db.query(TABLE_NAME, usernameColumnNameAsArray, "ID = ?", new String[] {stopId}, null, null, null);

	if(c.moveToNext()) {			if(c.moveToNext()) {
	found = true;			found = true;
	}			}

	c.close();			c.close();
	} catch(SQLiteException ignored) {			} catch(SQLiteException ignored) {
	// don't care			// don't care
	}			}

	return found;			return found;
	}			}

	/**			/**
	* Gets stop name set by the user.			* Gets stop name set by the user.
	*			*
	* @param db readable database			* @param db readable database
	* @param stopID stop ID			* @param stopID stop ID
	* @return name set by user, or null if not set\not found			* @return name set by user, or null if not set\not found
	*/			*/
	public static String getStopUserName(SQLiteDatabase db, String stopID) {			public static String getStopUserName(SQLiteDatabase db, String stopID) {
	String username = null;			String username = null;

	try {			try {
	Cursor c = db.query(TABLE_NAME, usernameColumnNameAsArray, "ID = ?", new String[] {stopID}, null, null, null);			Cursor c = db.query(TABLE_NAME, usernameColumnNameAsArray, "ID = ?", new String[] {stopID}, null, null, null);

	if(c.moveToNext()) {			if(c.moveToNext()) {
	username = c.getString(c.getColumnIndex("username"));			username = c.getString(c.getColumnIndex("username"));
	}			}
	c.close();			c.close();
	} catch(SQLiteException ignored) {}			} catch(SQLiteException ignored) {}

	return username;			return username;
	}			}

	public static List<Stop> getFavorites(SQLiteDatabase db, StopsDBInterface dbi) {			public static List<Stop> getFavorites(SQLiteDatabase db, StopsDBInterface dbi) {
	List<Stop> l = new ArrayList<>();			List<Stop> l = new ArrayList<>();
	Stop s;			Stop s;
	String stopID, stopUserName;			String stopID, stopUserName;

	try {			try {
	Cursor c = db.query(TABLE_NAME, getFavoritesColumnNamesAsArray, null, null, null, null, null, null);			Cursor c = db.query(TABLE_NAME, getFavoritesColumnNamesAsArray, null, null, null, null, null, null);
	int colID = c.getColumnIndex("ID");			int colID = c.getColumnIndex("ID");
	int colUser = c.getColumnIndex("username");			int colUser = c.getColumnIndex("username");

	while(c.moveToNext()) {			while(c.moveToNext()) {
	stopUserName = c.getString(colUser);			stopUserName = c.getString(colUser);
	stopID = c.getString(colID);			stopID = c.getString(colID);

	s = dbi.getAllFromID(stopID);			s = dbi.getAllFromID(stopID);

	if(s == null) {			if(s == null) {
	// can't find it in database			// can't find it in database
	l.add(new Stop(stopUserName, stopID, null, null, null));			l.add(new Stop(stopUserName, stopID, null, null, null));
	} else {			} else {
	// setStopName() already does sanity checks			// setStopName() already does sanity checks
	s.setStopUserName(stopUserName);			s.setStopUserName(stopUserName);
	l.add(s);			l.add(s);
	}			}
	}			}

	c.close();			c.close();
	} catch(SQLiteException ignored) {}			} catch(SQLiteException ignored) {}

	// comparison rules are too complicated to let SQLite do this (e.g. it outputs: 3234, 34, 576, 67, 8222) and stop name is in another database			// comparison rules are too complicated to let SQLite do this (e.g. it outputs: 3234, 34, 576, 67, 8222) and stop name is in another database
	Collections.sort(l);			Collections.sort(l);

	return l;			return l;
	}			}

	public static boolean addOrUpdateStop(Stop s, SQLiteDatabase db) {			public static boolean addOrUpdateStop(Stop s, SQLiteDatabase db) {
	ContentValues cv = new ContentValues();			ContentValues cv = new ContentValues();
	long result = -1;			long result = -1;
	String un = s.getStopUserName();			String un = s.getStopUserName();

	cv.put("ID", s.ID);			cv.put("ID", s.ID);
	// is there an username?			// is there an username?
	if(un == null) {			if(un == null) {
	// no: see if it's in the database			// no: see if it's in the database
	cv.put("username", getStopUserName(db, s.ID));			cv.put("username", getStopUserName(db, s.ID));
	} else {			} else {
	// yes: use it			// yes: use it
	cv.put("username", un);			cv.put("username", un);
	}			}

	try {			try {
	//ignore and throw -1 if the row is already in the DB			//ignore and throw -1 if the row is already in the DB
	result = db.insertWithOnConflict(TABLE_NAME, null, cv,SQLiteDatabase.CONFLICT_IGNORE);			result = db.insertWithOnConflict(TABLE_NAME, null, cv,SQLiteDatabase.CONFLICT_IGNORE);
	} catch (SQLiteException ignored) {}			} catch (SQLiteException ignored) {}

	// Android Studio suggested this unreadable replacement: return true if insert succeeded (!= -1), or try to update and return			// Android Studio suggested this unreadable replacement: return true if insert succeeded (!= -1), or try to update and return
	return (result != -1) \|\| updateStop(s, db);			return (result != -1) \|\| updateStop(s, db);
	}			}

	public static boolean updateStop(Stop s, SQLiteDatabase db) {			public static boolean updateStop(Stop s, SQLiteDatabase db) {
	try {			try {
	ContentValues cv = new ContentValues();			ContentValues cv = new ContentValues();
	cv.put("username", s.getStopUserName());			cv.put("username", s.getStopUserName());
	db.update(TABLE_NAME, cv, "ID = ?", new String[]{s.ID});			db.update(TABLE_NAME, cv, "ID = ?", new String[]{s.ID});
	return true;			return true;
	} catch(SQLiteException e) {			} catch(SQLiteException e) {
	return false;			return false;
	}			}
	}			}

	public static boolean deleteStop(Stop s, SQLiteDatabase db) {			public static boolean deleteStop(Stop s, SQLiteDatabase db) {
	try {			try {
	db.delete(TABLE_NAME, "ID = ?", new String[]{s.ID});			db.delete(TABLE_NAME, "ID = ?", new String[]{s.ID});
	return true;			return true;
	} catch(SQLiteException e) {			} catch(SQLiteException e) {
	return false;			return false;
	}			}
	}			}
	}			}