Differential D40 Diff 142 src/it/reyboz/bustorino/middleware/NextGenDB.java

Changeset View

Standalone View

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

Show All 14 Lines	BusTO (middleware)
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.content.Context;		import android.content.Context;
import android.content.SharedPreferences;		import android.content.SharedPreferences;
		import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;		import android.database.sqlite.SQLiteConstraintException;
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.provider.BaseColumns;		import android.provider.BaseColumns;
		import android.support.annotation.Nullable;
import android.util.Log;		import android.util.Log;
import it.reyboz.bustorino.R;		import it.reyboz.bustorino.R;
		import it.reyboz.bustorino.backend.Route;
		import it.reyboz.bustorino.backend.Stop;

		import java.util.*;
		import java.util.concurrent.atomic.AtomicInteger;

import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*;		import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*;

public class NextGenDB extends SQLiteOpenHelper{		public class NextGenDB extends SQLiteOpenHelper{
public static final String DATABASE_NAME = "bustodatabase.db";		public static final String DATABASE_NAME = "bustodatabase.db";
public static final int DATABASE_VERSION = 2;		public static final int DATABASE_VERSION = 2;
//Singleton instance		public static final String DEBUG_TAG = "NextGenDB-BusTO";
private static volatile NextGenDB instance = null;		//NO Singleton instance
		//private static volatile NextGenDB instance = null;
//Some generating Strings		//Some generating Strings
private static final String SQL_CREATE_LINES_TABLE="CREATE TABLE "+Contract.LinesTable.TABLE_NAME+" ("+		private static final String SQL_CREATE_LINES_TABLE="CREATE TABLE "+Contract.LinesTable.TABLE_NAME+" ("+
Contract.LinesTable._ID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+ Contract.LinesTable.COLUMN_NAME +" TEXT, "+		Contract.LinesTable._ID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+ Contract.LinesTable.COLUMN_NAME +" TEXT, "+
Contract.LinesTable.COLUMN_DESCRIPTION +" TEXT, "+Contract.LinesTable.COLUMN_TYPE +" TEXT, "+		Contract.LinesTable.COLUMN_DESCRIPTION +" TEXT, "+Contract.LinesTable.COLUMN_TYPE +" TEXT, "+
"UNIQUE ("+LinesTable.COLUMN_NAME+","+LinesTable.COLUMN_DESCRIPTION+","+LinesTable.COLUMN_TYPE+" ) "+" )";		"UNIQUE ("+LinesTable.COLUMN_NAME+","+LinesTable.COLUMN_DESCRIPTION+","+LinesTable.COLUMN_TYPE+" ) "+" )";

private static final String SQL_CREATE_BRANCH_TABLE="CREATE TABLE "+Contract.BranchesTable.TABLE_NAME+" ("+		private static final String SQL_CREATE_BRANCH_TABLE="CREATE TABLE "+Contract.BranchesTable.TABLE_NAME+" ("+
Contract.BranchesTable._ID +" INTEGER, "+ Contract.BranchesTable.COL_BRANCHID +" INTEGER PRIMARY KEY, "+		Contract.BranchesTable._ID +" INTEGER, "+ Contract.BranchesTable.COL_BRANCHID +" INTEGER PRIMARY KEY, "+
Show All 20 Lines	private static final String SQL_CREATE_STOPS_TABLE="CREATE TABLE "+Contract.StopsTable.TABLE_NAME+" ("+
Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";		Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";

private static final String SQL_CREATE_STOPS_TABLE_TO_COMPLETE = " ("+		private static final String SQL_CREATE_STOPS_TABLE_TO_COMPLETE = " ("+
Contract.StopsTable.COL_ID+" TEXT PRIMARY KEY, "+ Contract.StopsTable.COL_TYPE+" INTEGER, "+Contract.StopsTable.COL_LAT+" REAL NOT NULL, "+		Contract.StopsTable.COL_ID+" TEXT PRIMARY KEY, "+ Contract.StopsTable.COL_TYPE+" INTEGER, "+Contract.StopsTable.COL_LAT+" REAL NOT NULL, "+
Contract.StopsTable.COL_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+		Contract.StopsTable.COL_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+
Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+		Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+
Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";		Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";

		private static final String[] QUERY_COLUMN_stops_all = {
		StopsTable.COL_ID, StopsTable.COL_NAME, StopsTable.COL_LOCATION,
		StopsTable.COL_TYPE, StopsTable.COL_LAT, StopsTable.COL_LONG, StopsTable.COL_LINES_STOPPING};

		private static final String QUERY_WHERE_LAT_AND_LNG_IN_RANGE = StopsTable.COL_LAT + " >= ? AND " +
		StopsTable.COL_LAT + " <= ? AND "+ StopsTable.COL_LONG +
		" >= ? AND "+ StopsTable.COL_LONG + " <= ?";


private Context appContext;		private Context appContext;

private NextGenDB(Context context) {		public NextGenDB(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);		super(context, DATABASE_NAME, null, DATABASE_VERSION);
appContext = context.getApplicationContext();		appContext = context.getApplicationContext();
}		}

/**		/**
* Lazy initialization singleton getter, thread-safe with double checked locking		* Lazy initialization singleton getter, thread-safe with double checked locking
* from https://en.wikipedia.org/wiki/Singleton_pattern		* from https://en.wikipedia.org/wiki/Singleton_pattern
* @param context needed context
* @return the instance		* @return the instance
*/		*/
		/*
public static NextGenDB getInstance(Context context){		public static NextGenDB getInstance(Context context){
if(instance==null){		if(instance==null){
synchronized (NextGenDB.class){		synchronized (NextGenDB.class){
if(instance==null){		if(instance==null){
instance = new NextGenDB(context);		instance = new NextGenDB(context);
}		}
}		}
}		}
return instance;		return instance;
}		}*/

@Override		@Override
public void onCreate(SQLiteDatabase db) {		public void onCreate(SQLiteDatabase db) {
Log.d("BusTO-AppDB","Lines creating database:\n"+SQL_CREATE_LINES_TABLE+"\n"+		Log.d("BusTO-AppDB","Lines creating database:\n"+SQL_CREATE_LINES_TABLE+"\n"+
SQL_CREATE_STOPS_TABLE+"\n"+SQL_CREATE_BRANCH_TABLE+"\n"+SQL_CREATE_CONNECTIONS_TABLE);		SQL_CREATE_STOPS_TABLE+"\n"+SQL_CREATE_BRANCH_TABLE+"\n"+SQL_CREATE_CONNECTIONS_TABLE);
db.execSQL(SQL_CREATE_LINES_TABLE);		db.execSQL(SQL_CREATE_LINES_TABLE);

db.execSQL(SQL_CREATE_STOPS_TABLE);		db.execSQL(SQL_CREATE_STOPS_TABLE);
Show All 23 Lines	public class NextGenDB extends SQLiteOpenHelper{
}		}

@Override		@Override
public void onConfigure(SQLiteDatabase db) {		public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);		super.onConfigure(db);
db.execSQL("PRAGMA foreign_keys=ON");		db.execSQL("PRAGMA foreign_keys=ON");

}		}

public static String getSqlCreateStopsTable(String tableName){		public static String getSqlCreateStopsTable(String tableName){

return "CREATE TABLE "+tableName+" ("+		return "CREATE TABLE "+tableName+" ("+
Contract.StopsTable.COL_ID+" TEXT PRIMARY KEY, "+ Contract.StopsTable.COL_TYPE+" INTEGER, "+Contract.StopsTable.COL_LAT+" REAL NOT NULL, "+		Contract.StopsTable.COL_ID+" TEXT PRIMARY KEY, "+ Contract.StopsTable.COL_TYPE+" INTEGER, "+Contract.StopsTable.COL_LAT+" REAL NOT NULL, "+
Contract.StopsTable.COL_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+		Contract.StopsTable.COL_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+
Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+		Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+
Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";		Contract.StopsTable.COL_LINES_STOPPING +" TEXT )";
}		}

/**		/**
		* Query some bus stops inside a map view
		*
		* You can obtain the coordinates from OSMDroid using something like this:
		* BoundingBoxE6 bb = mMapView.getBoundingBox();
		* double latFrom = bb.getLatSouthE6() / 1E6;
		* double latTo = bb.getLatNorthE6() / 1E6;
		* double lngFrom = bb.getLonWestE6() / 1E6;
		* double lngTo = bb.getLonEastE6() / 1E6;
		*/
		public synchronized Stop[] queryAllInsideMapView(double minLat, double maxLat, double minLng, double maxLng) {
		Stop[] stops = new Stop[0];
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor result;
		int count;

		// coordinates must be strings in the where condition
		String minLatRaw = String.valueOf(minLat);
		String maxLatRaw = String.valueOf(maxLat);
		String minLngRaw = String.valueOf(minLng);
		String maxLngRaw = String.valueOf(maxLng);

		String[] queryColumns = {};
		String stopID;
		Route.Type type;

		if(db == null) {
		return stops;
		}

		try {
		result = db.query(StopsTable.TABLE_NAME, QUERY_COLUMN_stops_all, QUERY_WHERE_LAT_AND_LNG_IN_RANGE,
		new String[] {minLatRaw, maxLatRaw, minLngRaw, maxLngRaw},
		null, null, null);

		int colID = result.getColumnIndex(StopsTable.COL_ID);
		int colName = result.getColumnIndex(StopsTable.COL_NAME);
		int colLocation = result.getColumnIndex(StopsTable.COL_LOCATION);
		int colType = result.getColumnIndex(StopsTable.COL_TYPE);
		int colLat = result.getColumnIndex(StopsTable.COL_LAT);
		int colLon = result.getColumnIndex(StopsTable.COL_LONG);
		int colLines = result.getColumnIndex(StopsTable.COL_LINES_STOPPING);

		count = result.getCount();
		stops = new Stop[count];

		int i = 0;
		while(result.moveToNext()) {

		stopID = result.getString(colID);
		type = Route.getTypeFromSymbol(result.getString(colType));
		String lines = result.getString(colLines).trim();

		String locationSometimesEmpty = result.getString(colLocation);
		if (locationSometimesEmpty!= null && locationSometimesEmpty.length() <= 0) {
		locationSometimesEmpty = null;
		}

		stops[i++] = new Stop(stopID, result.getString(colName), null,
		locationSometimesEmpty, type, splitLinesString(lines),
		result.getDouble(colLat), result.getDouble(colLon));
		}

		} catch(SQLiteException e) {
		Log.e(DEBUG_TAG, "SQLiteException occurred");
		e.printStackTrace();
		return stops;
		}

		result.close();
		db.close();

		return stops;
		}

		/**
* Insert batch content, already prepared as		* Insert batch content, already prepared as
* @param content ContentValues array		* @param content ContentValues array
* @return number of lines inserted		* @return number of lines inserted
*/		*/
public int insertBatchContent(ContentValues[] content,String tableName) throws SQLiteException {		public int insertBatchContent(ContentValues[] content,String tableName) throws SQLiteException {

final SQLiteDatabase db = this.getWritableDatabase();		final SQLiteDatabase db = this.getWritableDatabase();
int success = 0;		int success = 0;
Show All 11 Lines	public int insertBatchContent(ContentValues[] content,String tableName) throws SQLiteException {
Log.w("NextGenDB_Insert", e);		Log.w("NextGenDB_Insert", e);
}		}
}		}
db.setTransactionSuccessful();		db.setTransactionSuccessful();
db.endTransaction();		db.endTransaction();
return success;		return success;
}		}

		public static List<String> splitLinesString(String linesStr){
		return Arrays.asList(linesStr.split("\\s,\\s"));
		}

public static final class Contract{		public static final class Contract{
//Ok, I get it, it really is a pain in the ass..		//Ok, I get it, it really is a pain in the ass..
// But it's the only way to have maintainable code		// But it's the only way to have maintainable code
public interface DataTables {		public interface DataTables {
String getTableName();		String getTableName();
String[] getFields();		String[] getFields();
}		}

▲ Show 20 Lines • Show All 90 Lines • Show Last 20 Lines