diff --git a/build.gradle b/build.gradle index de7f1be..7eaabd5 100644 --- a/build.gradle +++ b/build.gradle @@ -1,96 +1,99 @@ buildscript { repositories { jcenter() maven { url 'https://maven.google.com' } google() } dependencies { classpath 'com.android.tools.build:gradle:3.2.0' } ext { fragment_version = "1.2.5" activity_version = "1.1.0" appcompat_version = "1.2.0" preference_version = "1.1.1" + work_version = "2.4.0" } } allprojects { repositories { jcenter() maven { url 'https://maven.google.com' } google() } } apply plugin: 'com.android.application' android { compileSdkVersion 28 buildToolsVersion '29.0.3' defaultConfig { applicationId "it.reyboz.bustorino" minSdkVersion 14 targetSdkVersion 28 versionCode 30 versionName "1.13.1" vectorDrawables.useSupportLibrary = true } compileOptions { sourceCompatibility JavaVersion.VERSION_1_8 targetCompatibility JavaVersion.VERSION_1_8 } sourceSets { main { manifest.srcFile 'AndroidManifest.xml' java.srcDirs = ['src'] resources.srcDirs = ['src'] aidl.srcDirs = ['src'] renderscript.srcDirs = ['src'] res.srcDirs = ['res'] assets.srcDirs = ['assets'] } } buildTypes { debug { applicationIdSuffix ".debug" versionNameSuffix "-dev" } } lintOptions { abortOnError false } repositories { jcenter() mavenLocal() } dependencies { //new libraries implementation "androidx.fragment:fragment:$fragment_version" implementation "androidx.activity:activity:$activity_version" implementation "androidx.annotation:annotation:1.1.0" implementation "androidx.swiperefreshlayout:swiperefreshlayout:1.1.0" implementation "androidx.appcompat:appcompat:$appcompat_version" implementation "androidx.appcompat:appcompat-resources:$appcompat_version" implementation "androidx.preference:preference:$preference_version" + implementation "androidx.work:work-runtime:$work_version" + implementation 'com.google.android.material:material:1.2.1' implementation 'org.jsoup:jsoup:1.11.3' implementation 'com.readystatesoftware.sqliteasset:sqliteassethelper:2.0.1' implementation 'com.android.volley:volley:1.1.1' implementation 'org.osmdroid:osmdroid-android:6.1.8' } } diff --git a/src/it/reyboz/bustorino/middleware/DBUpdateWorker.java b/src/it/reyboz/bustorino/middleware/DBUpdateWorker.java new file mode 100644 index 0000000..f876055 --- /dev/null +++ b/src/it/reyboz/bustorino/middleware/DBUpdateWorker.java @@ -0,0 +1,92 @@ +package it.reyboz.bustorino.middleware; + +import android.content.Context; +import android.content.SharedPreferences; +import androidx.annotation.NonNull; +import androidx.work.*; +import it.reyboz.bustorino.R; +import it.reyboz.bustorino.backend.Fetcher; + +import java.util.concurrent.TimeUnit; +import java.util.concurrent.atomic.AtomicReference; + +import static android.content.Context.MODE_PRIVATE; + +public class DBUpdateWorker extends Worker{ + + + public static final String ERROR_CODE_KEY ="Error_Code"; + public static final String ERROR_REASON_KEY = "ERROR_REASON"; + public static final int ERROR_FETCHING_VERSION = 4; + public static final int ERROR_DOWNLOADING_STOPS = 5; + public static final int ERROR_DOWNLOADING_LINES = 6; + + public static final String SUCCESS_REASON_KEY = "SUCCESS_REASON"; + public static final int SUCCESS_NO_ACTION_NEEDED = 9; + public static final int SUCCESS_UPDATE_DONE = 1; + + + public DBUpdateWorker(@NonNull Context context, @NonNull WorkerParameters workerParams) { + super(context, workerParams); + } + + @NonNull + @Override + public Result doWork() { + final Context con = getApplicationContext(); + final SharedPreferences shPr = con.getSharedPreferences(con.getString(R.string.mainSharedPreferences),MODE_PRIVATE); + final int current_DB_version = shPr.getInt(DatabaseUpdate.DB_VERSION_KEY,-1); + + final int new_DB_version = DatabaseUpdate.getNewVersion(); + if (new_DB_version < 0){ + //there has been an error + final Data out = new Data.Builder().putInt(ERROR_REASON_KEY, ERROR_FETCHING_VERSION) + .putInt(ERROR_CODE_KEY,new_DB_version).build(); + return Result.failure(out); + } + //we got a good version + if (current_DB_version >= new_DB_version) { + //don't need to update + return Result.success(new Data.Builder(). + putInt(SUCCESS_REASON_KEY, SUCCESS_NO_ACTION_NEEDED).build()); + } + //start the real update + AtomicReference resultAtomicReference = new AtomicReference<>(); + DatabaseUpdate.setDBUpdatingFlag(con, shPr,true); + final DatabaseUpdate.Result resultUpdate = DatabaseUpdate.performDBUpdate(con,resultAtomicReference); + DatabaseUpdate.setDBUpdatingFlag(con, shPr,false); + + if (resultUpdate != DatabaseUpdate.Result.DONE){ + Fetcher.result result = resultAtomicReference.get(); + + final Data.Builder dataBuilder = new Data.Builder(); + switch (resultUpdate){ + case ERROR_STOPS_DOWNLOAD: + dataBuilder.put(ERROR_REASON_KEY, ERROR_DOWNLOADING_STOPS); + break; + case ERROR_LINES_DOWNLOAD: + dataBuilder.put(ERROR_REASON_KEY, ERROR_DOWNLOADING_LINES); + break; + } + return Result.failure(dataBuilder.build()); + } + //update the version in the shared preference + final SharedPreferences.Editor editor = shPr.edit(); + editor.putInt(DatabaseUpdate.DB_VERSION_KEY, new_DB_version); + editor.apply(); + + return Result.success(new Data.Builder().putInt(SUCCESS_REASON_KEY, SUCCESS_UPDATE_DONE).build()); + } + + public static Constraints getWorkConstraints(){ + return new Constraints.Builder().setRequiredNetworkType(NetworkType.CONNECTED) + .setRequiresCharging(false).build(); + } + + public static WorkRequest newFirstTimeWorkRequest(){ + return new OneTimeWorkRequest.Builder(DBUpdateWorker.class) + .setBackoffCriteria(BackoffPolicy.LINEAR, 15, TimeUnit.SECONDS) + .build(); + } + +} diff --git a/src/it/reyboz/bustorino/middleware/DatabaseUpdate.java b/src/it/reyboz/bustorino/middleware/DatabaseUpdate.java new file mode 100644 index 0000000..660ef94 --- /dev/null +++ b/src/it/reyboz/bustorino/middleware/DatabaseUpdate.java @@ -0,0 +1,158 @@ +package it.reyboz.bustorino.middleware; + +import android.content.ContentValues; +import android.content.Context; +import android.content.SharedPreferences; +import android.database.sqlite.SQLiteDatabase; +import android.util.Log; +import it.reyboz.bustorino.R; +import it.reyboz.bustorino.backend.Fetcher; +import it.reyboz.bustorino.backend.FiveTAPIFetcher; +import it.reyboz.bustorino.backend.Route; +import it.reyboz.bustorino.backend.Stop; +import org.json.JSONException; +import org.json.JSONObject; + +import java.util.ArrayList; +import java.util.concurrent.atomic.AtomicReference; + +import static android.content.Context.MODE_PRIVATE; + + +public class DatabaseUpdate { + + public static final String DEBUG_TAG = "BusTO-DBUpdate"; + public static final int VERSION_UNAVAILABLE = -2; + public static final int JSON_PARSING_ERROR = -4; + + public static final String DB_VERSION_KEY = "NextGenDB.GTTVersion"; + + enum Result { + DONE, ERROR_STOPS_DOWNLOAD, ERROR_LINES_DOWNLOAD + } + + /** + * Request the server the version of the database + * @return the version of the DB, or an error code + */ + public static int getNewVersion(){ + AtomicReference gres = new AtomicReference<>(); + String networkRequest = FiveTAPIFetcher.performAPIRequest(FiveTAPIFetcher.QueryType.STOPS_VERSION,null,gres); + if(networkRequest == null){ + return VERSION_UNAVAILABLE; + } + + try { + JSONObject resp = new JSONObject(networkRequest); + return resp.getInt("id"); + } catch (JSONException e) { + e.printStackTrace(); + Log.e(DEBUG_TAG,"Error: wrong JSON response\nResponse:\t"+networkRequest); + return JSON_PARSING_ERROR; + } + } + /** + * Run the DB Update + * @param con a context + * @param gres a result reference + * @return result of the update + */ + public static Result performDBUpdate(Context con, AtomicReference gres) { + + final FiveTAPIFetcher f = new FiveTAPIFetcher(); + final ArrayList stops = f.getAllStopsFromGTT(gres); + //final ArrayList cpOp = new ArrayList<>(); + + if (gres.get() != Fetcher.result.OK) { + Log.w(DEBUG_TAG, "Something went wrong downloading"); + return Result.ERROR_STOPS_DOWNLOAD; + + } + // return false; //If the commit to the SharedPreferences didn't succeed, simply stop updating the database + final NextGenDB dbHelp = new NextGenDB(con.getApplicationContext()); + final SQLiteDatabase db = dbHelp.getWritableDatabase(); + //Empty the needed tables + db.beginTransaction(); + //db.execSQL("DELETE FROM "+StopsTable.TABLE_NAME); + //db.delete(LinesTable.TABLE_NAME,null,null); + + //put new data + long startTime = System.currentTimeMillis(); + + Log.d(DEBUG_TAG, "Inserting " + stops.size() + " stops"); + for (final Stop s : stops) { + final ContentValues cv = new ContentValues(); + + cv.put(NextGenDB.Contract.StopsTable.COL_ID, s.ID); + cv.put(NextGenDB.Contract.StopsTable.COL_NAME, s.getStopDefaultName()); + if (s.location != null) + cv.put(NextGenDB.Contract.StopsTable.COL_LOCATION, s.location); + cv.put(NextGenDB.Contract.StopsTable.COL_LAT, s.getLatitude()); + cv.put(NextGenDB.Contract.StopsTable.COL_LONG, s.getLongitude()); + if (s.getAbsurdGTTPlaceName() != null) cv.put(NextGenDB.Contract.StopsTable.COL_PLACE, s.getAbsurdGTTPlaceName()); + cv.put(NextGenDB.Contract.StopsTable.COL_LINES_STOPPING, s.routesThatStopHereToString()); + if (s.type != null) cv.put(NextGenDB.Contract.StopsTable.COL_TYPE, s.type.getCode()); + + //Log.d(DEBUG_TAG,cv.toString()); + //cpOp.add(ContentProviderOperation.newInsert(uritobeused).withValues(cv).build()); + //valuesArr[i] = cv; + db.replace(NextGenDB.Contract.StopsTable.TABLE_NAME, null, cv); + + } + db.setTransactionSuccessful(); + db.endTransaction(); + long endTime = System.currentTimeMillis(); + Log.d(DEBUG_TAG, "Inserting stops took: " + ((double) (endTime - startTime) / 1000) + " s"); + + final ArrayList routes = f.getAllLinesFromGTT(gres); + + if (routes == null) { + Log.w(DEBUG_TAG, "Something went wrong downloading the lines"); + dbHelp.close(); + return Result.ERROR_LINES_DOWNLOAD; + + } + + db.beginTransaction(); + startTime = System.currentTimeMillis(); + for (Route r : routes) { + final ContentValues cv = new ContentValues(); + cv.put(NextGenDB.Contract.LinesTable.COLUMN_NAME, r.getName()); + switch (r.type) { + case BUS: + cv.put(NextGenDB.Contract.LinesTable.COLUMN_TYPE, "URBANO"); + break; + case RAILWAY: + cv.put(NextGenDB.Contract.LinesTable.COLUMN_TYPE, "FERROVIA"); + break; + case LONG_DISTANCE_BUS: + cv.put(NextGenDB.Contract.LinesTable.COLUMN_TYPE, "EXTRA"); + break; + } + cv.put(NextGenDB.Contract.LinesTable.COLUMN_DESCRIPTION, r.description); + + //db.insert(LinesTable.TABLE_NAME,null,cv); + int rows = db.update(NextGenDB.Contract.LinesTable.TABLE_NAME, cv, NextGenDB.Contract.LinesTable.COLUMN_NAME + " = ?", new String[]{r.getName()}); + if (rows < 1) { //we haven't changed anything + db.insert(NextGenDB.Contract.LinesTable.TABLE_NAME, null, cv); + } + } + db.setTransactionSuccessful(); + db.endTransaction(); + endTime = System.currentTimeMillis(); + Log.d(DEBUG_TAG, "Inserting lines took: " + ((double) (endTime - startTime) / 1000) + " s"); + dbHelp.close(); + + return Result.DONE; + } + + public static boolean setDBUpdatingFlag(Context con, boolean value){ + final SharedPreferences shPr = con.getSharedPreferences(con.getString(R.string.mainSharedPreferences),MODE_PRIVATE); + return setDBUpdatingFlag(con, shPr, value); + } + static boolean setDBUpdatingFlag(Context con, SharedPreferences shPr,boolean value){ + final SharedPreferences.Editor editor = shPr.edit(); + editor.putBoolean(con.getString(R.string.databaseUpdatingPref),value); + return editor.commit(); + } +} diff --git a/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java b/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java index ae448ac..a5dbd19 100644 --- a/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java +++ b/src/it/reyboz/bustorino/middleware/DatabaseUpdateService.java @@ -1,277 +1,278 @@ /* BusTO (middleware) Copyright (C) 2018 Fabio Mazza 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 the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see . */ package it.reyboz.bustorino.middleware; import android.app.IntentService; import android.content.*; -import android.database.sqlite.SQLiteDatabase; import androidx.annotation.Nullable; import android.util.Log; import it.reyboz.bustorino.R; import it.reyboz.bustorino.backend.Fetcher; import it.reyboz.bustorino.backend.FiveTAPIFetcher; -import it.reyboz.bustorino.backend.Route; -import it.reyboz.bustorino.backend.Stop; import org.json.JSONException; import org.json.JSONObject; -import java.util.ArrayList; import java.util.concurrent.atomic.AtomicReference; -import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*; - /** * An {@link IntentService} subclass for handling asynchronous task requests in * a service on a separate handler thread. */ public class DatabaseUpdateService extends IntentService { // IntentService can perform, e.g. ACTION_FETCH_NEW_ITEMS private static final String ACTION_UPDATE = "it.reyboz.bustorino.middleware.action.UPDATE_DB"; private static final String DB_VERSION = "NextGenDB.GTTVersion"; private static final String DEBUG_TAG = "DatabaseService_BusTO"; // TODO: Rename parameters private static final String TRIAL = "it.reyboz.bustorino.middleware.extra.TRIAL"; private static final String COMPULSORY = "compulsory_update"; private static final int MAX_TRIALS = 5; private static final int VERSION_UNAIVALABLE = -2; public DatabaseUpdateService() { super("DatabaseUpdateService"); } private boolean isRunning; private int updateTrial; /** * Starts this service to perform action Foo with the given parameters. If * the service is already performing a task this action will be queued. * * @see IntentService */ public static void startDBUpdate(Context con, int trial, @Nullable Boolean mustUpdate){ Intent intent = new Intent(con, DatabaseUpdateService.class); intent.setAction(ACTION_UPDATE); intent.putExtra(TRIAL,trial); if(mustUpdate!=null){ intent.putExtra(COMPULSORY,mustUpdate); } con.startService(intent); } public static void startDBUpdate(Context con) { startDBUpdate(con, 0, false); } @Override protected void onHandleIntent(Intent intent) { if (intent != null) { final String action = intent.getAction(); if (ACTION_UPDATE.equals(action)) { Log.d(DEBUG_TAG,"Started action update"); SharedPreferences shPr = getSharedPreferences(getString(R.string.mainSharedPreferences),MODE_PRIVATE); int versionDB = shPr.getInt(DB_VERSION,-1); final int trial = intent.getIntExtra(TRIAL,-1); final SharedPreferences.Editor editor = shPr.edit(); updateTrial = trial; UpdateRequestParams params = new UpdateRequestParams(intent); int newVersion = getNewVersion(params); if(newVersion==VERSION_UNAIVALABLE){ //NOTHING LEFT TO DO return; } Log.d(DEBUG_TAG,"newDBVersion: "+newVersion+" oldVersion: "+versionDB); if(params.mustUpdate || versionDB==-1 || newVersion>versionDB){ Log.d(DEBUG_TAG,"Downloading the bus stops info"); final AtomicReference gres = new AtomicReference<>(); if(!performDBUpdate(gres)) restartDBUpdateifPossible(params,gres); else { editor.putInt(DB_VERSION,newVersion); // BY COMMENTING THIS, THE APP WILL CONTINUOUSLY UPDATE THE DATABASE editor.apply(); } } else { Log.d(DEBUG_TAG,"No update needed"); } Log.d(DEBUG_TAG,"Finished update"); setDBUpdatingFlag(shPr,false); } } } private boolean setDBUpdatingFlag(SharedPreferences shPr,boolean value){ final SharedPreferences.Editor editor = shPr.edit(); editor.putBoolean(getString(R.string.databaseUpdatingPref),value); return editor.commit(); } private boolean setDBUpdatingFlag(boolean value){ final SharedPreferences shPr = getSharedPreferences(getString(R.string.mainSharedPreferences),MODE_PRIVATE); return setDBUpdatingFlag(shPr,value); } private boolean performDBUpdate(AtomicReference gres){ + if(!setDBUpdatingFlag(true)) + return false; + + /* final FiveTAPIFetcher f = new FiveTAPIFetcher(); final ArrayList stops = f.getAllStopsFromGTT(gres); //final ArrayList cpOp = new ArrayList<>(); if(gres.get()!= Fetcher.result.OK){ Log.w(DEBUG_TAG,"Something went wrong downloading"); return false; } if(!setDBUpdatingFlag(true)) return false; //If the commit to the SharedPreferences didn't succeed, simply stop updating the database final NextGenDB dbHelp = new NextGenDB(getApplicationContext()); final SQLiteDatabase db = dbHelp.getWritableDatabase(); //Empty the needed tables db.beginTransaction(); //db.execSQL("DELETE FROM "+StopsTable.TABLE_NAME); //db.delete(LinesTable.TABLE_NAME,null,null); //put new data long startTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting "+stops.size()+" stops"); for (final Stop s : stops) { final ContentValues cv = new ContentValues(); cv.put(StopsTable.COL_ID, s.ID); cv.put(StopsTable.COL_NAME, s.getStopDefaultName()); if (s.location != null) cv.put(StopsTable.COL_LOCATION, s.location); cv.put(StopsTable.COL_LAT, s.getLatitude()); cv.put(StopsTable.COL_LONG, s.getLongitude()); if (s.getAbsurdGTTPlaceName() != null) cv.put(StopsTable.COL_PLACE, s.getAbsurdGTTPlaceName()); cv.put(StopsTable.COL_LINES_STOPPING, s.routesThatStopHereToString()); if (s.type != null) cv.put(StopsTable.COL_TYPE, s.type.getCode()); //Log.d(DEBUG_TAG,cv.toString()); //cpOp.add(ContentProviderOperation.newInsert(uritobeused).withValues(cv).build()); //valuesArr[i] = cv; db.replace(StopsTable.TABLE_NAME,null,cv); } db.setTransactionSuccessful(); db.endTransaction(); long endTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting stops took: "+((double) (endTime-startTime)/1000)+" s"); final ArrayList routes = f.getAllLinesFromGTT(gres); if(routes==null){ Log.w(DEBUG_TAG,"Something went wrong downloading the lines"); dbHelp.close(); return false; } db.beginTransaction(); startTime = System.currentTimeMillis(); for (Route r: routes){ final ContentValues cv = new ContentValues(); cv.put(LinesTable.COLUMN_NAME,r.getName()); switch (r.type){ case BUS: cv.put(LinesTable.COLUMN_TYPE,"URBANO"); break; case RAILWAY: cv.put(LinesTable.COLUMN_TYPE,"FERROVIA"); break; case LONG_DISTANCE_BUS: cv.put(LinesTable.COLUMN_TYPE,"EXTRA"); break; } cv.put(LinesTable.COLUMN_DESCRIPTION,r.description); //db.insert(LinesTable.TABLE_NAME,null,cv); int rows = db.update(LinesTable.TABLE_NAME,cv,LinesTable.COLUMN_NAME+" = ?",new String[]{r.getName()}); if(rows<1){ //we haven't changed anything db.insert(LinesTable.TABLE_NAME,null,cv); } } db.setTransactionSuccessful(); db.endTransaction(); endTime = System.currentTimeMillis(); Log.d(DEBUG_TAG,"Inserting lines took: "+((double) (endTime-startTime)/1000)+" s"); dbHelp.close(); return true; + + */ + return DatabaseUpdate.performDBUpdate(getApplication(),gres) == DatabaseUpdate.Result.DONE; } private int getNewVersion(UpdateRequestParams params){ AtomicReference gres = new AtomicReference<>(); String networkRequest = FiveTAPIFetcher.performAPIRequest(FiveTAPIFetcher.QueryType.STOPS_VERSION,null,gres); if(networkRequest == null){ restartDBUpdateifPossible(params,gres); return VERSION_UNAIVALABLE; } boolean needed; try { JSONObject resp = new JSONObject(networkRequest); return resp.getInt("id"); } catch (JSONException e) { e.printStackTrace(); Log.e(DEBUG_TAG,"Error: wrong JSON response\nResponse:\t"+networkRequest); return -4; } } private void restartDBUpdateifPossible(UpdateRequestParams pars, AtomicReference res){ if (pars.trial. */ package it.reyboz.bustorino.middleware; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteConstraintException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.provider.BaseColumns; import androidx.annotation.Nullable; import android.util.Log; +import it.reyboz.bustorino.backend.Fetcher; +import it.reyboz.bustorino.backend.FiveTAPIFetcher; import it.reyboz.bustorino.backend.Route; import it.reyboz.bustorino.backend.Stop; +import org.json.JSONException; +import org.json.JSONObject; import java.util.*; +import java.util.concurrent.atomic.AtomicReference; import static it.reyboz.bustorino.middleware.NextGenDB.Contract.*; public class NextGenDB extends SQLiteOpenHelper{ public static final String DATABASE_NAME = "bustodatabase.db"; public static final int DATABASE_VERSION = 2; public static final String DEBUG_TAG = "NextGenDB-BusTO"; //NO Singleton instance //private static volatile NextGenDB instance = null; //Some generating Strings 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.COLUMN_DESCRIPTION +" TEXT, "+Contract.LinesTable.COLUMN_TYPE +" TEXT, "+ "UNIQUE ("+LinesTable.COLUMN_NAME+","+LinesTable.COLUMN_DESCRIPTION+","+LinesTable.COLUMN_TYPE+" ) "+" )"; 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.COL_LINE +" INTEGER, "+ Contract.BranchesTable.COL_DESCRIPTION +" TEXT, "+ Contract.BranchesTable.COL_DIRECTION+" TEXT, "+ Contract.BranchesTable.COL_TYPE +" INTEGER, "+ //SERVICE DAYS: 0 => FERIALE,1=>FESTIVO,-1=>UNKNOWN,add others if necessary Contract.BranchesTable.COL_FESTIVO +" INTEGER, "+ //DAYS COLUMNS. IT'S SO TEDIOUS I TRIED TO KILL MYSELF BranchesTable.COL_LUN+" INTEGER, "+BranchesTable.COL_MAR+" INTEGER, "+BranchesTable.COL_MER+" INTEGER, "+BranchesTable.COL_GIO+" INTEGER, "+ BranchesTable.COL_VEN+" INTEGER, "+ BranchesTable.COL_SAB+" INTEGER, "+BranchesTable.COL_DOM+" INTEGER, "+ "FOREIGN KEY("+ Contract.BranchesTable.COL_LINE +") references "+ Contract.LinesTable.TABLE_NAME+"("+ Contract.LinesTable._ID+") " +")"; private static final String SQL_CREATE_CONNECTIONS_TABLE="CREATE TABLE "+Contract.ConnectionsTable.TABLE_NAME+" ("+ Contract.ConnectionsTable.COLUMN_BRANCH+" INTEGER, "+ Contract.ConnectionsTable.COLUMN_STOP_ID+" TEXT, "+ Contract.ConnectionsTable.COLUMN_ORDER+" INTEGER, "+ "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_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+" ("+ 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_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" TEXT, "+ Contract.StopsTable.COL_LINES_STOPPING +" TEXT )"; 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_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+ Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" 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; public NextGenDB(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); appContext = context.getApplicationContext(); } - /** - * Lazy initialization singleton getter, thread-safe with double checked locking - * from https://en.wikipedia.org/wiki/Singleton_pattern - * @return the instance - */ - /* - public static NextGenDB getInstance(Context context){ - if(instance==null){ - synchronized (NextGenDB.class){ - if(instance==null){ - instance = new NextGenDB(context); - } - } - } - return instance; - }*/ @Override public void onCreate(SQLiteDatabase db) { 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); db.execSQL(SQL_CREATE_LINES_TABLE); db.execSQL(SQL_CREATE_STOPS_TABLE); //tables with constraints db.execSQL(SQL_CREATE_BRANCH_TABLE); db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if(oldVersion<2 && newVersion == 2){ //DROP ALL TABLES db.execSQL("DROP TABLE "+ConnectionsTable.TABLE_NAME); db.execSQL("DROP TABLE "+BranchesTable.TABLE_NAME); db.execSQL("DROP TABLE "+LinesTable.TABLE_NAME); db.execSQL("DROP TABLE "+ StopsTable.TABLE_NAME); //RECREATE THE TABLES WITH THE NEW SCHEMA db.execSQL(SQL_CREATE_LINES_TABLE); db.execSQL(SQL_CREATE_STOPS_TABLE); //tables with constraints db.execSQL(SQL_CREATE_BRANCH_TABLE); db.execSQL(SQL_CREATE_CONNECTIONS_TABLE); DatabaseUpdateService.startDBUpdate(appContext,0,true); } } @Override public void onConfigure(SQLiteDatabase db) { super.onConfigure(db); db.execSQL("PRAGMA foreign_keys=ON"); } public static String getSqlCreateStopsTable(String 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_LONG+" REAL NOT NULL, "+ Contract.StopsTable.COL_NAME+" TEXT NOT NULL, "+ Contract.StopsTable.COL_LOCATION+" TEXT, "+Contract.StopsTable.COL_PLACE+" 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 * @param content ContentValues array * @return number of lines inserted */ public int insertBatchContent(ContentValues[] content,String tableName) throws SQLiteException { final SQLiteDatabase db = this.getWritableDatabase(); int success = 0; db.beginTransaction(); for (final ContentValues cv : content) { try { db.replaceOrThrow(tableName, null, cv); success++; } catch (SQLiteConstraintException d){ Log.w("NextGenDB_Insert","Failed insert with FOREIGN KEY... \n"+d.getMessage()); } catch (Exception e) { Log.w("NextGenDB_Insert", e); } } db.setTransactionSuccessful(); db.endTransaction(); return success; } public static List splitLinesString(String linesStr){ return Arrays.asList(linesStr.split("\\s*,\\s*")); } public static final class Contract{ //Ok, I get it, it really is a pain in the ass.. // But it's the only way to have maintainable code public interface DataTables { String getTableName(); String[] getFields(); } public static final class LinesTable implements BaseColumns, DataTables { //The fields public static final String TABLE_NAME = "lines"; public static final String COLUMN_NAME = "line_name"; public static final String COLUMN_DESCRIPTION = "line_description"; public static final String COLUMN_TYPE = "line_bacino"; @Override public String getTableName() { return TABLE_NAME; } @Override public String[] getFields() { return new String[]{COLUMN_NAME,COLUMN_DESCRIPTION,COLUMN_TYPE}; } } public static final class BranchesTable implements BaseColumns, DataTables { public static final String TABLE_NAME = "branches"; public static final String COL_BRANCHID = "branchid"; public static final String COL_LINE = "lineid"; public static final String COL_DESCRIPTION = "branch_description"; public static final String COL_DIRECTION = "branch_direzione"; public static final String COL_FESTIVO = "branch_festivo"; public static final String COL_TYPE = "branch_type"; public static final String COL_LUN="runs_lun"; public static final String COL_MAR="runs_mar"; public static final String COL_MER="runs_mer"; public static final String COL_GIO="runs_gio"; public static final String COL_VEN="runs_ven"; public static final String COL_SAB="runs_sab"; public static final String COL_DOM="runs_dom"; @Override public String getTableName() { return TABLE_NAME; } @Override public String[] getFields() { return new String[]{COL_BRANCHID,COL_LINE,COL_DESCRIPTION, COL_DIRECTION,COL_FESTIVO,COL_TYPE, COL_LUN,COL_MAR,COL_MER,COL_GIO,COL_VEN,COL_SAB,COL_DOM }; } } public static final class ConnectionsTable implements DataTables { public static final String TABLE_NAME = "connections"; public static final String COLUMN_BRANCH = "branchid"; public static final String COLUMN_STOP_ID = "stopid"; public static final String COLUMN_ORDER = "ordine"; @Override public String getTableName() { return TABLE_NAME; } @Override public String[] getFields() { return new String[]{COLUMN_STOP_ID,COLUMN_BRANCH,COLUMN_ORDER}; } } public static final class StopsTable implements DataTables { public static final String TABLE_NAME = "stops"; public static final String COL_ID = "stopid"; //integer public static final String COL_TYPE = "stop_type"; public static final String COL_NAME = "stop_name"; public static final String COL_LAT = "stop_latitude"; public static final String COL_LONG = "stop_longitude"; public static final String COL_LOCATION = "stop_location"; public static final String COL_PLACE = "stop_placeName"; public static final String COL_LINES_STOPPING = "stop_lines"; @Override public String getTableName() { return TABLE_NAME; } @Override public String[] getFields() { return new String[]{COL_ID,COL_TYPE,COL_NAME,COL_LAT,COL_LONG,COL_LOCATION,COL_PLACE,COL_LINES_STOPPING}; } } } public static final class DBUpdatingException extends Exception{ public DBUpdatingException(String message) { super(message); } } - }