Changeset View
Changeset View
Standalone 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 |
Public contents are in Creative Commons Attribution-ShareAlike 4.0 (CC-BY-SA) or GNU Free Documentation License (at your option) unless otherwise noted. · Contact / Register