Changeset View
Changeset View
Standalone View
Standalone View
src/it/reyboz/bustorino/data/NextGenDB.java
Show All 30 Lines | |||||
import it.reyboz.bustorino.backend.Stop; | import it.reyboz.bustorino.backend.Stop; | ||||
import java.util.*; | import java.util.*; | ||||
import static it.reyboz.bustorino.data.NextGenDB.Contract.*; | import static it.reyboz.bustorino.data.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 = 3; | ||||
public static final String DEBUG_TAG = "NextGenDB-BusTO"; | public static final String DEBUG_TAG = "NextGenDB-BusTO"; | ||||
//NO Singleton instance | //NO Singleton instance | ||||
//private static volatile NextGenDB instance = null; | //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+" ) "+" )"; | ||||
Show All 14 Lines | private static final String SQL_CREATE_CONNECTIONS_TABLE="CREATE TABLE "+Contract.ConnectionsTable.TABLE_NAME+" ("+ | ||||
Contract.ConnectionsTable.COLUMN_ORDER+" INTEGER, "+ | Contract.ConnectionsTable.COLUMN_ORDER+" INTEGER, "+ | ||||
"PRIMARY KEY ("+ Contract.ConnectionsTable.COLUMN_BRANCH+","+ Contract.ConnectionsTable.COLUMN_STOP_ID + "), "+ | "PRIMARY KEY ("+ Contract.ConnectionsTable.COLUMN_BRANCH+","+ Contract.ConnectionsTable.COLUMN_STOP_ID + "), "+ | ||||
"FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_BRANCH+") references "+ Contract.BranchesTable.TABLE_NAME+"("+ Contract.BranchesTable.COL_BRANCHID +"), "+ | "FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_BRANCH+") references "+ Contract.BranchesTable.TABLE_NAME+"("+ Contract.BranchesTable.COL_BRANCHID +"), "+ | ||||
"FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_STOP_ID+") references "+ Contract.StopsTable.TABLE_NAME+"("+ Contract.StopsTable.COL_ID +") " | "FOREIGN KEY("+ Contract.ConnectionsTable.COLUMN_STOP_ID+") references "+ Contract.StopsTable.TABLE_NAME+"("+ Contract.StopsTable.COL_ID +") " | ||||
+")"; | +")"; | ||||
private static final String SQL_CREATE_STOPS_TABLE="CREATE TABLE "+Contract.StopsTable.TABLE_NAME+" ("+ | private static final String SQL_CREATE_STOPS_TABLE="CREATE TABLE "+Contract.StopsTable.TABLE_NAME+" ("+ | ||||
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, "+ | ||||
StopsTable.COL_GTFS_ID+" TEXT, "+ | |||||
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 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 )"; | ||||
public static final String[] QUERY_COLUMN_stops_all = { | public static final String[] QUERY_COLUMN_stops_all = { | ||||
StopsTable.COL_ID, StopsTable.COL_NAME, StopsTable.COL_LOCATION, | StopsTable.COL_ID, StopsTable.COL_NAME, StopsTable.COL_GTFS_ID, StopsTable.COL_LOCATION, | ||||
StopsTable.COL_TYPE, StopsTable.COL_LAT, StopsTable.COL_LONG, StopsTable.COL_LINES_STOPPING}; | StopsTable.COL_TYPE, StopsTable.COL_LAT, StopsTable.COL_LONG, StopsTable.COL_LINES_STOPPING}; | ||||
public static final String QUERY_WHERE_LAT_AND_LNG_IN_RANGE = StopsTable.COL_LAT + " >= ? AND " + | public static final String QUERY_WHERE_LAT_AND_LNG_IN_RANGE = StopsTable.COL_LAT + " >= ? AND " + | ||||
StopsTable.COL_LAT + " <= ? AND "+ StopsTable.COL_LONG + | StopsTable.COL_LAT + " <= ? AND "+ StopsTable.COL_LONG + | ||||
" >= ? AND "+ StopsTable.COL_LONG + " <= ?"; | " >= ? AND "+ StopsTable.COL_LONG + " <= ?"; | ||||
public static String QUERY_WHERE_ID = StopsTable.COL_ID+" = ?"; | public static String QUERY_WHERE_ID = StopsTable.COL_ID+" = ?"; | ||||
Show All 31 Lines | public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { | ||||
db.execSQL(SQL_CREATE_LINES_TABLE); | db.execSQL(SQL_CREATE_LINES_TABLE); | ||||
db.execSQL(SQL_CREATE_STOPS_TABLE); | db.execSQL(SQL_CREATE_STOPS_TABLE); | ||||
//tables with constraints | //tables with constraints | ||||
db.execSQL(SQL_CREATE_BRANCH_TABLE); | db.execSQL(SQL_CREATE_BRANCH_TABLE); | ||||
db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); | db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); | ||||
DatabaseUpdate.requestDBUpdateWithWork(appContext, true); | DatabaseUpdate.requestDBUpdateWithWork(appContext, true); | ||||
} | } | ||||
if(oldVersion < 3 && newVersion == 3){ | |||||
Log.d("BusTO-Database", "Running upgrades for version 3"); | |||||
//add the new column | |||||
db.execSQL("ALTER TABLE "+StopsTable.TABLE_NAME+ | |||||
" ADD COLUMN "+StopsTable.COL_GTFS_ID+" TEXT "); | |||||
// DatabaseUpdate.requestDBUpdateWithWork(appContext, true); | |||||
} | |||||
} | } | ||||
@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"); | ||||
} | } | ||||
▲ Show 20 Lines • Show All 68 Lines • ▼ Show 20 Lines | public static Stop[] getStopsFromCursorAllFields(Cursor result){ | ||||
int count = result.getCount(); | int count = result.getCount(); | ||||
Stop[] stops = new Stop[count]; | Stop[] stops = new Stop[count]; | ||||
int i = 0; | int i = 0; | ||||
while(result.moveToNext()) { | while(result.moveToNext()) { | ||||
final String stopID = result.getString(colID).trim(); | final String stopID = result.getString(colID).trim(); | ||||
final Route.Type type = Route.getTypeFromSymbol(result.getString(colType)); | final Route.Type type; | ||||
if(result.getString(colType) == null) type = Route.Type.BUS; | |||||
else type = Route.getTypeFromSymbol(result.getString(colType)); | |||||
String lines = result.getString(colLines).trim(); | String lines = result.getString(colLines).trim(); | ||||
String locationSometimesEmpty = result.getString(colLocation); | String locationSometimesEmpty = result.getString(colLocation); | ||||
if (locationSometimesEmpty!= null && locationSometimesEmpty.length() <= 0) { | if (locationSometimesEmpty!= null && locationSometimesEmpty.length() <= 0) { | ||||
locationSometimesEmpty = null; | locationSometimesEmpty = null; | ||||
} | } | ||||
stops[i++] = new Stop(stopID, result.getString(colName), null, | stops[i++] = new Stop(stopID, result.getString(colName), null, | ||||
Show All 26 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; | ||||
} | } | ||||
int updateLinesStoppingInStop(List<Stop> stops){ | |||||
return 0; | |||||
} | |||||
public static List<String> splitLinesString(String linesStr){ | public static List<String> splitLinesString(String linesStr){ | ||||
return Arrays.asList(linesStr.split("\\s*,\\s*")); | 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 { | ||||
▲ Show 20 Lines • Show All 62 Lines • ▼ Show 20 Lines | public static final class Contract{ | ||||
return new String[]{COLUMN_STOP_ID,COLUMN_BRANCH,COLUMN_ORDER}; | return new String[]{COLUMN_STOP_ID,COLUMN_BRANCH,COLUMN_ORDER}; | ||||
} | } | ||||
} | } | ||||
public static final class StopsTable implements DataTables { | public static final class StopsTable implements DataTables { | ||||
public static final String TABLE_NAME = "stops"; | public static final String TABLE_NAME = "stops"; | ||||
public static final String COL_ID = "stopid"; //integer | public static final String COL_ID = "stopid"; //integer | ||||
public static final String COL_TYPE = "stop_type"; | public static final String COL_TYPE = "stop_type"; | ||||
public static final String COL_NAME = "stop_name"; | public static final String COL_NAME = "stop_name"; | ||||
public static final String COL_GTFS_ID = "gtfs_id"; | |||||
public static final String COL_LAT = "stop_latitude"; | public static final String COL_LAT = "stop_latitude"; | ||||
public static final String COL_LONG = "stop_longitude"; | public static final String COL_LONG = "stop_longitude"; | ||||
public static final String COL_LOCATION = "stop_location"; | public static final String COL_LOCATION = "stop_location"; | ||||
public static final String COL_PLACE = "stop_placeName"; | public static final String COL_PLACE = "stop_placeName"; | ||||
public static final String COL_LINES_STOPPING = "stop_lines"; | public static final String COL_LINES_STOPPING = "stop_lines"; | ||||
@Override | @Override | ||||
public String getTableName() { | public String getTableName() { | ||||
return TABLE_NAME; | return TABLE_NAME; | ||||
} | } | ||||
@Override | @Override | ||||
public String[] getFields() { | public String[] getFields() { | ||||
return new String[]{COL_ID,COL_TYPE,COL_NAME,COL_LAT,COL_LONG,COL_LOCATION,COL_PLACE,COL_LINES_STOPPING}; | return new String[]{COL_ID,COL_TYPE,COL_NAME,COL_GTFS_ID,COL_LAT,COL_LONG,COL_LOCATION,COL_PLACE,COL_LINES_STOPPING}; | ||||
} | } | ||||
} | } | ||||
} | } | ||||
public static final class DBUpdatingException extends Exception{ | public static final class DBUpdatingException extends Exception{ | ||||
public DBUpdatingException(String message) { | public DBUpdatingException(String message) { | ||||
super(message); | super(message); | ||||
} | } | ||||
} | } | ||||
} | } |
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